Access Queries to the same excel spread sheet

Vinod Godghate
Vinod Godghate used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Rgonzo1971
Top Expert 2016

Commented:
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
Commented:
Thanks for the prompt comment

But if I run the command .......

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


The Commad window for MACRO get opened.
the excel file is not created .

Thanks
Rgonzo1971
Top Expert 2016

Commented:
which message error?
Vinod Godghate

Author

Commented:
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
Rgonzo1971
Top Expert 2016

Commented:
A solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial