Avatar of Vinod Godghate
Vinod Godghate
 asked on

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

Avatar of undefined
Last Comment
Rgonzo1971

8/22/2022 - Mon
Rgonzo1971

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

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

which message error?
Vinod Godghate

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

A solution