Access Export multiple queries to same Excel Spreadsheet

Dear Experts

I'm not sure if this is even possible - I want to export 2 Access Queries to the same excel spread sheet so query 1 goes to sheet 1 & query 2 goes to sheet 2.  I am using Access & Excel 2010

For various reasons this has to be an export rather than simply linking the excel spread sheet back to Access.

Can anybody help with some code to achieve this

Many thanks
correlateAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this copy and paste

Private Sub ExportAnalysis_Click()
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, "Sheet1"
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True,"Sheet2"
End Sub
0
 
gowflowCommented:
Not too much into Access but your doing this viaa VBA in Access or from the menu manually ?
gowflow
0
 
Rgonzo1971Commented:
Hi,

pls try

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "myQry1", "c:\myFile.xlsx", True, , "Sheet1!A1:C1"

Regards
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
correlateAuthor Commented:
Hi, thanks for the rapid response ..

gowflow - I'm looking at using VBA, triggered from a command button in a form

Rgonzo1971 - I have tried your code, alas I get an error - 2498 "An expression you entered is the wrong data type for one of the arguments - the code I've entered is below - Any Ideas?

Private Sub ExportAnalysis_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, , "Sheet1!A1:E1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, , "Sheet2!A1:E1"
End Sub

Open in new window

0
 
gowflowCommented:
Just omit
acSpreadsheetTypeExcel12Xml

try
Private Sub ExportAnalysis_Click()
DoCmd.TransferSpreadsheet acExport, , "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, , "Sheet1!A1:E1"
DoCmd.TransferSpreadsheet acExport, , "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, , "Sheet2!A1:E1"
End Sub

Open in new window


gowflow
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't use a Range value when exporting:

Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
From here: https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

Only way I know to do this is through automation.
0
 
correlateAuthor Commented:
Hi

Thanks for that - gave it a go, but got the same error message I'm afraid
0
 
Dale FyeCommented:
Check out this response to a previous question for instructions on how to use automation and the CopyFromRecordset method to export Access data to a specific location in your workbook.
0
 
correlateAuthor Commented:
Brilliant, works a treat - thank you very much
0
All Courses

From novice to tech pro — start learning today.