Export a query result to acess

[TS]

Hi all,
I have a query that I want to export the results of to an Excel spreadsheet.
This query will be generated every day and thus the data it holds will be
updated everyday. I want to add a button to a form that outputs the results
to the Excel sheet accumulatively (the query result in everyday should be
appended to the Excel sheet, not replacing it). What's the code for that?

 

[John Nurick]

Hi TS,

You can juse Get External Data|Link to access the Excel sheet as a
linked table. Then convert your query to an append query that appends
its data to the linked table; rows appended to the linked table will be
added to the bottom of the worksheet.

 

[TS]

Thanks for your reply. The reason why I can't use an append query is that the
query will generate one row of record every day -the query is a cross tab
query-. The record for the new day should be appended to the Excel sheet. For
example the query result on the first day will look like this in the EXCEL
sheet:-
Date ProgramX ProgramY ProgramZ
1/25/2005 70(Total # Clients) 385 145

The following day I will run the crosstab query that will look something
like this:
Date ProgramX ProgramY ProgramZ
1/26/2005 72 383 146

I want to output the result of the Access query of that following day to the
EXCEL sheet to look like this:
Date ProgramX ProgramY ProgramZ
1/25/2005 70 385 145
1/26/2005 72 383 146

And so forth for the rest of the days in the month. How to accomplishthis? I
used before the open - copy recordset syntax to copy queries results to
specific cells in EXCEL, but in this case maybe I need to use some increment
syntax to keep adding rows of the query records to the excel sheet. I need
help with the code. Thank you so much

 

[John Nurick]

Have you tried using an append query? It works for me.

 

[TS]

Thanks a lot. I will try this. I am just curious, is there any Micosoft
documentation that talks about how to copy the result set of an access query
to the following empty row in an excel sheet?

 

[John Nurick]

I don't know a specific reference. It's mostly standard Excel
programming. The general idea would be (pseudocode)

-Launch an instance of Excel and open the workbook
-Scan down the worksheet until you find the bottom of the existing data
-Open a recordset on the query
-Do RecordSet.MoveLast and get the number of records in the recordset
-Compare the number of records in the recordset with the number of rows
available in the Excel sheet
-If there's room, use Excel's Range.CopyFromRecordset to paste the data
into the sheet.
-Save and close the workbook and the instance of Excel.