Saving Query Results in VB to an Existing File

[StayAtHomeMom]

I am using VB within my Access database (behind a button on a form) to run a query; output the results to Excel; open up that Excel file; format the file, and close it using the code below:

DoCmd.OutputTo acQuery, "RPT_BR002_Report", acFormatXLS,"C:\Brady\RPT_BR002.xls", False, "", 0

Workbooks.Open "C:\Brady\RPT_BR002.xls"

‘formatting;saving and closing the file code goes here

What I want to do is run the above query and save it to Excel as before, but not close the file so I can add a new sheet, run a different query, saving the results TO THAT SAME FILE, just to a different worksheet.

I am not having success with this because I’m using the same command, DoCmd.OutputTo acQuery… using the same file name. VB doesn’t like that. It gives me a runtime error "2302", "Access can't save the output data to the file you have selected"

Any suggestions on this? Again, what I’m trying to do is “reuse” an existing file to run more queries, and saving the results to different (new) worksheets.

Thanks for all your help and expertise!

 

[PHV]

Instead of using the OutputTo method you may try to play with the CopyFromRecordset method of the Excel.Range object.

Anyway, replace this:
Workbooks.Open "C:\Brady\RPT_BR002.xls"
By something like this:
yourXLobj.Workbooks.Open "C:\Brady\RPT_BR002.xls"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[webcats]

Have a look at this site... there's a piece of code that will export to an existing ss

http://www.danielklann.com/access/export_an_access_table.htm

 

[JerryKlmns]

StayAtHomeMom,

the following, uses the same excel file name (MonadesPolisis.xls) to export the result set of 4 different querries to 4 different sheets named after the query name that has been exported to that sheet.

DoCmd.TransferSpreadsheet acExport,  acSpreadsheetTypeExcel9, "Christos_AAL", Server_Location & "MonadesPolisis.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Christos_Terms", Server_Location & "MonadesPolisis.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Christos_Revolving", Server_Location & "MonadesPolisis.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Christos_T/X", Server_Location & "MonadesPolisis.xls", True

So export all your querries and then open the excel to do the formatting ...