Output Access query to named worksheet in Excel file

[Stuart]

Can I do this in Access 97?

I have an Excel file which is formatted for a dynamic
report with pivot tables etc. I've tried to link the data
sheet in the template to a file which only contains the
raw data output by Access, which is created each day.
However Excel often cannot find the donor file from the
template file, despite it being in the same directory.
I've discovered that this is an 'undocumented feature'of
Excel (thanks Bill!) so I have to find another way round
the issue.

Is there any way I can output my data directly to a named
worksheet the template file? I don't seem to be able to do
this through a macro, so are there any other wheezes you
can suggest?

Stuart

 

[Nikos Yannacopoulos]

Stuart,

Yes, you can do it with a macro in Access. Use a TransferSpreadsheet action,
specify the table or query to be exported and the workbook path and name in
the obvious arguments, and then the target worksheet name in the Range
argument. The table /query will be exported in the named worksheet; if it
does not already exist, it will be created, if it does, it will be
overwritten.
This is again an undocumented feature in Access; the help on
TransferSpreadsheet says the Range argument is not used for output.

HTH,
Nikos

 

[Stuart]

Thanks Nikos

I have a Greek colleague. When he heard your name, he
wondered if you were the same person he knew in school in
Ioannina around 1991.

Regards

Stuart
([email protected])

 

[Nikos Yannacopoulos]

Stuart,

No. I'm not the same person. I didn't go to school at Ioannina, and I sure
wish I were still at school age in 1991!

Nikos