Solved: Query to existing Excel spreadsheet

[hillaryruth]

I have this code which creates a spreadsheet, Tbl1.xls with the results.
How can this code be changed to add the results of the query, when run, to an existing spreadsheet. I don't want it to override the existing data in the spreadsheet. I know nothing about VB, please make changes to my example. Thanks


Private Sub Command4_Click()
'Produce "Tbl1XLS" excel spread****.
'***********************************
DoCmd.OutputTo acOutputQuery, "Query2", acFormatXLS, "Tbl1.xls", True
End Sub

[XLGibbs]

Easiest way is actually to link the excel file directly to the access Query using MS Query. You can point a query right at your "query2" and set the properties to append the data instead of overwrite.

It is possible to follow that statement with code which opens tbl1.xls and moves the data to the other spreadsheet...

There is lots of code samples here that show how to open one workbook and copy data to a location in another..perhaps that is an option for you to explore?

[hillaryruth]

I'm looking for somone to show me how to either change this code or show me some other code and tell me where to put it to make this work.
I would like to do push the button and it will send the results of the Access Query2 to an existing spreadsheet called caseloadmgt.xls which is located on my desktop - C:\Documents and Settings\Jim.Black\Desktop\caseloadmgt.xls
And the next time I push the button to run the query again I want the new data to start 2 cells below the ending of the first query results.



Private Sub Command4_Click()
'Produce "caseloadmgt.xls" excel spread****.
'***********************************
DoCmd.OutputTo acOutputQuery, "Query2", acFormatXLS, "caseloadmgt.xls", True
End Sub

I have posted this same issue to Access World Forums with no replies

[XLGibbs]

DoCmd.OutputTo acOutputQuery, "Query2", acFormatXLS, " C:\Documents and Settings\Jim.Black\Desktop\caseloadmgt.xls", True

I am pretty sure you have to specify the full path there.

[hillaryruth]

Thanks! That worked ok