Transfer Spreadsheet Export

[Guest]

Hi

I am trying to use the transfer spreadsheet function to export a crosstab
query to a named worksheet in a workbook.

The worksheet already exists and when i call this function it creates a
"worksheet1".

Is it possible for it to overwrite the existing worksheet without renaming it?

I have look up functions that reference this worksheet and therefore i
cannot just create a new worksheet, the formulas will fail.

Please accept my thanks

rgds
Colin

 

[Ken Snell [MVP]]

Not by using TransferSpreadsheet.

You'd need to use Automation to open the EXCEL file, delete the spreadsheet,
and then do the export.

 

[Nikos Yannacopoulos]

Colin

Yes, it is possible, at least in A2K. If you look up TrabsferSpreadsheet in
Access help, you'll see that the Range argument is only used for import.
Well, guess what, it works for Export and it does exactl what you want! Put
the existing worksheet name in there, and it will be overwritten, keeping
its name.

HTH,
Nikos