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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Rgonzo1971

A solution