How to export and add data to an Excel worksheet?

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

Guest

I have an Export statement:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"PHQChartQuery", "C:\PHQChart.xls", False

There is a file already in that location named the same thing
I would like to substitute the new data on the worksheet PHQChartQuery that
is already in the file PHQChart.

The reason I would like to do this is I have a graph on that Excel file that
I would like applied to the data I would be putting into it.

When I export it just adds another worksheet called PHQChartQuery1 but does
not have the graph information like it does on PHQChartQuery.

So is this possible?
 
It's possible. You'll need to write Access VBA code that uses Automation
to launch Excel and open the workbook; then open a recordset on the
query and use Excel's CopyFromRecordset method to place the data into
the worksheet.

If you search the web or the newsgroups (e.g. at Google Groups) for some
of the above keywords you should find examples of the sort of coding
involved.
 
Back
Top