Export a query result to acess

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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
 
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?
 
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.
 
Back
Top