Export Access Table data to Excel - DoCmd.TransferSpreadsheet function

[santa12345]

Hello.
I have a vb script in Access.
Its been working fine for years.
Basically the script uses a bunch of OpenQuery & TransferSpreadsheet Export commands.

DoCmd.OpenQuery "Sample Query1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Sample Query1 Export", "c:\text..xls", True

The XLS template currently has a sample_query1_export tab and the data gets replaced. Everything works great.

Now, I need to add an additional query and export of data to new tab as well.

My issue is, the export works, but it creates an additional tab. Let's say I need the export the data to tab ABC.
Tab ABC exist in the target (text.xls) file - but for some reason, this new export is creating an additional tab ABC_1..not ABC... in the excel template thus causing me errors as I have references in the excel file pointing to tab ABC not ABC_1.

Any ideas would be appreciated.

I'm lost on this one.

Thanks.

 

[alansidman]

See if this helps AccessBlog.net: Export to Excel range: Tips and tricks, news, links, downloads on Microsoft Access

 

[santa12345]

that was very helpful - thank you....

 

[alansidman]

thanks for the feedback.