Export multiple Access queries to Excel 1

[Steven547]

I have multiple queries (for this example, query 1, and query 2). I have a cmd btn on the form, that when clicked, it runs a macro, which runs the queries, then, exports that data to an excel spreasheet. However, I would like EACH query to have its OWN tab in the same spreadsheet. In the macro, I tried outputto and transferspreadsheet, but they both overwrite each other. I've searched on here (obviously not good enough I guess) but haven't found an easy method to my madness. Can anyone assist me in exporting multiple access queries to different tabs in the same excel spreadsheet?

Thank you.

 

[Steven547]

Currently, I have this on the click event of a command button:
DoCmd.OutputTo acOutputQuery, "qryEffectiveTrustees", , "TestXLS.xls"
DoCmd.OutputTo acOutputQuery, "qryEffectiveTrustees2", , "TestXLS.xls"
DoCmd.OutputTo acOutputQuery, "qryEffectiveTrustees3", , "TestXLS.xls"

The problem is, the outputs overwrite each excel file. I would like each one of these in the same excel file, but different tab (worksheet).

 

[PHV]

What about this ?
DoCmd.TransferSpreadsheet acExport, , "qryEffectiveTrustees", "TestXLS.xls", True
DoCmd.TransferSpreadsheet acExport, , "qryEffectiveTrustees2", "TestXLS.xls", True
DoCmd.TransferSpreadsheet acExport, , "qryEffectiveTrustees3", "TestXLS.xls", True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Steven547]

Yes...that works wonders for me! Thank you very much. All the books I have mention the "transferspreadsheet", but don't give examples or how to use that.

 

[Remou]

Steven547
You can use the link to thank people, too:

Thank <person>
for this valuable post!

 

[Steven547]

Done!

 

[Steven547]

Sorry to bother again...one question in regards to the above:

my code:
DoCmd.TransferSpreadsheet acExport, , , "qryEffectiveTrusteesMain", "TestXLS.xls", True

Works great, but if I have an unbound text box, how can I have it so the user enters the name of the File they want the excel file to be? So I have an ubound text box: txtNameOfFile

How do incorporate that into the code above? I tried a forms.forms.... and that didnt' work... any ideas?

thanks.

 

[Steven547]

This is what i've tried:

DoCmd.TransferSpreadsheet acExport, , , "qryEffectiveTrusteesMain", (Me.txtMigFileName) & ".xls", True

where txtMigFileName is the text box where the user enters the name of the file. I get a "An expression you entered is the wrong data type for one of the arguements.

 

[PHV]

Get rid of one comma:
DoCmd.TransferSpreadsheet acExport, , "qryEffectiveTrusteesMain", Me!txtMigFileName & ".xls", True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

You seem to have too many commas:

DoCmd.TransferSpreadsheet acExport, , "qryEffectiveTrusteesMain", (Me.txtMigFileName) & ".xls", True

 

[Steven547]

That is super! Thank you. Is there a site you could refer me to that would give those examples? The books I have just talk about the transferspreadsheet, but do not supply any examples. Such as what the commas mean, etc..etc..

Thank you.

 

[Remou]

Is VBA help not installed on your computer?