Export multiple queries to Excel

[educate889]

I need help!

I have a series of queries that are used at month end and need to automate the process of dumping data into Excel.

I can get them to export to Excel, but I need each query in it's own worksheet within the main workbook.

Here is the code I am presently using, it is in public function just for trials on my part. This works but then overwrites the first query ... I think I am just missing a line of code (or 2) that creates and names the worksheet tabs in excel.

Public Function Create_Export()
On Error GoTo Create_Export_Err


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


Create_Export_Exit:
Exit Function

Create_Export_Err:
MsgBox Error$
Resume Create_Export_Exit

End Function



Any help would be greatly appreciated.

 

[dougcranston]

educate889,

I recently had a similar requirement.

However, my approach was to use VBScript to connect to Access using ADO, run queries to get the necessary data and create individual worksheets, name the tab, format the spreadsheet and then email it.

However I did not/could not use them to work with DOCMD since I was working from VBScript.

Did this so I can automate my report generation using AT or Task Scheduler to create the workbook and then email it to my users.

If interested create a msg with your email address and I will send you a copy of the script... way to long for this environment, sans my database.

Hope this helps.

DougCranston

 

[educate889]

Sounds complicated for me . . but I would sure like to give it a shot . . . hopefully I can do it inside of Access and get it to create the other worksheets per query within the single workbook

Please send to

brians@xymoxtech.com