Export to Excel ... 2

[educate889]

Hello experts...

I need to be able to export multiple queries to an Excel workbook and have each query create a new worksheet tab.

I can get it to export but the last query is all that is left after it overwrites the one before. Is there a way to create/name a new tab on the fly??? I hope so. . .

I appreciate any help you may give!

'------------------------------------------------------------
' Export queries to Excel Spreadsheet in users H:
'
'------------------------------------------------------------
Public Function Create_Export()
On Error GoTo Create_Export_Err


DoCmd.SetWarnings False
DoCmd.OpenQuery "XCM102_SLSTOTALS", acViewNormal, acEdit
DoCmd.OutputTo acQuery, "", "MicrosoftExcel(*.xls)", "H:\XCM_RawData2.xls", False, ""
DoCmd.Close acQuery, "XCM102_slsTOTALS", acSavePrompt
DoCmd.OpenQuery "XCM101_ICTOTALS", acViewNormal, acEdit
DoCmd.OutputTo acQuery, "", "MicrosoftExcel(*.xls)", "H:\XCM_RawData2.xls", True, ""
DoCmd.Close acQuery, "XCM101_ICTOTALS", acSavePrompt
DoCmd.SetWarnings True

Create_Export_Exit:
Exit Function

Create_Export_Err:
MsgBox Error$
Resume Create_Export_Exit

End Function

 

[educate889]

Thank you both . . . you have definately helped me and I hope the stars show that. . .

I will try lewakin's idea tomorrow at work. . .

I still am having the naming problem but will try Paul's idea . . .

If that is the worst case that can't be fixed I will survive.

Thanks again for all your help!