export queries to excel?

[AccessIdiot]

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!

[ADezii]

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?

Dim qdf As DAO.QueryDef
 
For Each qdf In CurrentDb.QueryDefs
  'Filter out Temporary Querys
  If Left$(qdf.Name, 1) <> "~" Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, "C:\Test\" & qdf.Name & ".xls", True
  End If
Next

[AccessIdiot]

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?

Dim qdf As DAO.QueryDef
 
For Each qdf In CurrentDb.QueryDefs
  'Filter out Temporary Querys
  If Left$(qdf.Name, 1) <> "~" Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, "C:\Test\" & qdf.Name & ".xls", True
  End If
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!

[ADezii]

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'.

[AccessIdiot]

4. That's what I was afraid of. :( Hint hint MS!!!

Thanks for answering my questions!

[ADezii]

4. That's what I was afraid of. :( Hint hint MS!!!

Thanks for answering my questions!

You are quite welcome.