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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.