Transfer Spreadsheet method Not replacing Sheet Name

D

DoctorV

I have this line of code which Transfers one record of a query and make
a Worksheet named A_EXPORT. This worked fine until I started havin
some linked references in the workbook to this newly created shee
A_EXPORT

Now instead of replacing the sheet every time I transfer data fro
Access to Excel it is adding a sheet named A_EXPORT1 etc
How can I get this to replace the sheet or can I copy the recordset t
row 2 of A_EXPORT instead?
EXTREMELY IMPORTANT!!!!!!

DoCmd.TransferSpreadsheet acExport, 8, "A_EXPORT", str, True, ""
FollowHyperlink st
 
D

Debra Dalgleish

When you export the data, a range with the same name as the query is
created. For example, a query named "qryMyData" will export to a range
named "qryMyData" on a worksheet named "qryMyData".

If the range name is deleted, a new sheet is created.
 
J

Jamie Collins

Debra Dalgleish wrote ...
When you export the data, a range with the same name as the query is
created. For example, a query named "qryMyData" will export to a range
named "qryMyData" on a worksheet named "qryMyData".

If the range name is deleted, a new sheet is created.

Interesting. Sound's like under the hood it's issuing a

DROP TABLE <<Excel range>>;

before issuing a

SELECT * INTO <<Excel range>> FROM <<Table>>;

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top