'Overwrite' an existing Excel spreadsheet with new data from Acces

[Ralph]

I have an excel spreadsheet that calculates a multitiude of data from one
worksheet with named ranges to another worksheet. I have a nifty Access DB
that imports a 'raw data' file and through a series of query's builds a
finished table named "My new data" that I can import to the Excel file.
Since the Excel file has a worksheet with the names built in already, I would
like to 'replace' the worksheet named "My_new_data" with new data each month.
I use the command: DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,"My new data", "C:\MYFILE.XLS",False
which transfers the table OK -- but-- Excel automatically sees that I
already have a worksheet with this name, so rather than 'overwrite' the
existing data it adds another worksheet with the name "My_new_data1" which of
course does NOT contain my range names. I can write an Excel macro to fix
this, but there may be an easier way????? Thanks!!

 

[John Nurick]

Hi Ralph,

I'd investigate the following. First, define a named range on the
worksheet consisting of the top left cell of where you want to put the
data. Then, write code running in Access to:

1) Use Automation to launch Excel and open the workbook (see e.g.
http://www.mvps.org/access/modules/mdl0006.htm if this is new territory)

2) Open a recordset on your table.

3) Use Excel's Range.CopyFromRecordset method to paste the data into the
correct place on the worksheet.