Access Queries to the same excel spread sheet

Dear Experts

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

I am using the following codes

'------------------------------------------------------------
' ExportToExcel
'
'------------------------------------------------------------
Function ExportToExcel()
On Error GoTo ExportToExcel_Err

DoCmd.OutputTo acOutputQuery, "TRERequired_Summary", "Excel97-Excel2003Workbook(*.xls)", "C:\Users\OWNER\Desktop\Test\TRERequired_Summary.xls", False, "", , acExportQualityScreen
DoCmd.OutputTo acOutputQuery, "TRERequired", "Excel97-Excel2003Workbook(*.xls)", "C:\Users\OWNER\Desktop\Test\TRERequired_Summary.xls", False, "", , acExportQualityScreen

ExportToExcel_Exit:
Exit Function

ExportToExcel_Err:
MsgBox Error$
Resume ExportToExcel_Exit

End Function

Many thanks

Microsoft Access

Last Comment

Rgonzo1971

8/22/2022 - Mon

Rgonzo1971

4/4/2017

Hi,

pls try

Function ExportToExcel()
 On Error GoTo ExportToExcel_Err
     DoCmd.TransferSpreadsheet acExport, AcSpreadsheetTypeExcel9, "TRERequired_Summary", "C:\Users\OWNER\Desktop\Test\TRERequired_Summary.xls", , "Sheet1"
     DoCmd.TransferSpreadsheet acExport, AcSpreadsheetTypeExcel9, "TRERequired", "C:\Users\OWNER\Desktop\Test\TRERequired_Summary.xls", , "Sheet2"
     
ExportToExcel_Exit:
     Exit Function

ExportToExcel_Err:
     MsgBox Error$
     Resume ExportToExcel_Exit

 End Function

Open in new window

Regards

ASKER CERTIFIED SOLUTION

Vinod Godghate

4/4/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Rgonzo1971

4/4/2017

which message error?

Vinod Godghate

4/4/2017

ASKER

Thanks for the response . I used the following code IT WORKED . NOW IT IS FINE

'------------------------------------------------------------
' ExportToExcel
'
'------------------------------------------------------------
Function ExportToExcel()
On Error GoTo ExportToExcel_Err

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TRERequired_Summary", "C:\Users\OWNER\Desktop\Test\TRERequired_Summary.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TRERequired", "C:\Users\OWNER\Desktop\Test\TRERequired_Summary.xls", True

thanks
ExportToExcel_Exit:
Exit Function

ExportToExcel_Err:
MsgBox Error$
Resume ExportToExcel_Exit

End Function

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

Rgonzo1971

4/24/2017

A solution