Automatically exporting and appending multiple queries to individual tabs in Excel

[darls15]

Hi All
I currently use the DoCmd.TransferSpreadsheet sample code below to export multiple queries to a single Excel workbook on a weekly basis.
It works well in that each query is output to a individual tabs in the one workbook.
However, what I'd ultimately like to do, instead of creating a new file each time I export, is to have the data from each of the queries automatically append to the respective worksheets (query1, query2, query3).
I would like the data to be placed after the last row of data on each worksheet and the workbook to automatically save and close without being prompted.
I have searched extensively but can't seem to find anything that I have the skills to be able to modify that will do this.
Would someone please be able to help me with this?
Many thanks
darls15

~~~~~~~~~~~~~~~~~~~

Sub ExportQueryData()

Dim strFilePath As String
strFilePath = "C:\Output"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "exp_query1", strFilePath + "alldata.xlsx", True, "query1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "exp_query2", strFilePath + "alldata.xlsx", True, "query2"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "exp_query3", strFilePath + "alldata.xlsx", True, "query3"

End Sub

[June7]

See if something here http://accessmvp.com/KDSnell/EXCEL_Export.htm gets you close.

Should be able to use either automation or CopyFromRecordset to export to an existing Excel file. What you need in addition is code to locate next blank row to use as start location. Something like:

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

Workbooks can be unmanageable when they get too much data.
Excel can link to Access table or query.
Excel VBA can pull data from Access table or query.

[darls15]

Hi June7
Thanks for taking the time to get reply and thanks for the advice.
I'm now thinking I was going about this all wrong and will look into storing it in tables and linking from Excel instead.
Thanks again
darls15