TransferSpreadsheet in Access2000

[Guest]

How do I write code to transfer data into an existing worksheet/spreadsheet
and also have it put in a specific starting cell range? I can do the transfer
into a new spreadsheet but I want the data to be put in specific cells, etc.

 

[fredg]

How do I write code to transfer data into an existing worksheet/spreadsheet
and also have it put in a specific starting cell range? I can do the transfer
into a new spreadsheet but I want the data to be put in specific cells, etc.

From VBA help on the TransferSpreadsheet Range argument:

Range Optional Variant. A string expression that's a valid range of
cells or the name of a range in the spreadsheet. This argument applies
only to importing. Leave this argument blank to import the entire
spreadsheet. ***When you export to a spreadsheet, you must leave this
argument blank. If you enter a range, the export will fail.***

In general, make the transfer to the spreadsheet and then cut and copy
to your range from there.

 

[Guest]

So what you're saying is if I export to excel I can't specify a range to
export the data to or even the file to send it to?

 

[MacDermott]

What Fred is saying is that you can't do what you want using the
TransferSpreadsheet command.
I've done things similar to yours many times using Automation to open a copy
of Excel and manipulate it programmatically.
In taking this approach, I'd recommend that you consider using an Excel
template, and open a new file based on that template, rather than opening an
existing file, where you always run the risk of overwriting.

 

[Guest]

I understand what you're saying. As far as overwriting the data in excel I'm
not concerned with that. The data will need to be overwritten. How would I do
this using automation?