By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

export queries to excel?

AccessIdiot
100+
P: 493
Hi all,

I have a bunch of queries (about 40) that right now I am exporting one by one to excel. Is there a way to do a mass export? With the "save formatted" box checked? I don't want to have this as a button on a form - I just want to export everything in the Queries object in the database.

Thanks for any help!
Aug 8 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,669
Hi all,

I have a bunch of queries (about 40) that right now I am exporting one by one to excel. Is there a way to do a mass export? With the "save formatted" box checked? I don't want to have this as a button on a form - I just want to export everything in the Queries object in the database.

Thanks for any help!
The following code will Export all but the Temporary Queries ("~*") to an Excel 9.0 format in the C:\Test Directory. The Spreadsheets will have the Base Query Name with the .xls extension appended. Is this what you are looking for?
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2.  
  3. For Each qdf In CurrentDb.QueryDefs
  4.   'Filter out Temporary Querys
  5.   If Left$(qdf.Name, 1) <> "~" Then
  6.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, "C:\Test\" & qdf.Name & ".xls", True
  7.   End If
  8. Next
Aug 9 '07 #2

AccessIdiot
100+
P: 493
The following code will Export all but the Temporary Queries ("~*") to an Excel 9.0 format in the C:\Test Directory. The Spreadsheets will have the Base Query Name with the .xls extension appended. Is this what you are looking for?
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2.  
  3. For Each qdf In CurrentDb.QueryDefs
  4.   'Filter out Temporary Querys
  5.   If Left$(qdf.Name, 1) <> "~" Then
  6.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, "C:\Test\" & qdf.Name & ".xls", True
  7.   End If
  8. Next
Yes, but I do have a couple of questions - where do you typically put this code? How do you call it? Also, when I export one by one there is a check box for "save format" which will (for example) input "true" or "false" instead of "1" or "0" in the excel table. Can I accomplish this in the above code?

I was sort of hoping there was an "export all queries" choice in a menu within Access somewhere but I guess this doesn't exist? Oh I'm using Access 2003 by the way.

Thanks for your help!
Aug 9 '07 #3

ADezii
Expert 5K+
P: 8,669
Yes, but I do have a couple of questions - where do you typically put this code? How do you call it? Also, when I export one by one there is a check box for "save format" which will (for example) input "true" or "false" instead of "1" or "0" in the excel table. Can I accomplish this in the above code?

I was sort of hoping there was an "export all queries" choice in a menu within Access somewhere but I guess this doesn't exist? Oh I'm using Access 2003 by the way.

Thanks for your help!
  1. You can place this clode in the Click() Event of a Command Button.
  2. You call the code by just clicking on the above-mentioned Command Button.
  3. The TransferSpreadsheet Method has no provisions for maintaining or ignoring formatting options.
  4. There is no Menu witin Access where you can 'Export All Queries'.
Aug 9 '07 #4

AccessIdiot
100+
P: 493
4. That's what I was afraid of. :( Hint hint MS!!!

Thanks for answering my questions!
Aug 9 '07 #5

ADezii
Expert 5K+
P: 8,669
4. That's what I was afraid of. :( Hint hint MS!!!

Thanks for answering my questions!
You are quite welcome.
Aug 9 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.