Access to Excel

[jfsauzeat129 via AccessMonster.com]

I know this subject has been tackled several times but I wasn't able to find
the code to achieve this

I am exporting a query to an existing Excel file with several tabs.
I would like the query to replace the tabs they export every time I export
from Access.
Instead Excel creates a new tab, indexing the name of the tab everytime I
export.
I have the following code:

Dim sXL As String
Dim xlApp As Object
Dim strPath As String
sXL = "C:\\Quality\Critical Parameters Tracking\QR_CP.xls"
DoCmd.TransferSpreadsheet acExport, , "QR_CP_product1", sXL, True,
"QR_CP_product1"""

Thanks,

Jeff

 

[Ken Snell [MVP]]

See this article for explanation of why you see this behavior when you use
the Range argument for an export:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange


The best "fix" for your situation is to export a query that has the same
name as the worksheet into which you want the data to be exported. I have
various examples here:
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

 

[jfsauzeat129 via AccessMonster.com]

THANKS A LOT for the links.
The code works great now.

That was very helpful.

Jeff

See this article for explanation of why you see this behavior when you use
the Range argument for an export:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

The best "fix" for your situation is to export a query that has the same
name as the worksheet into which you want the data to be exported. I have
various examples here:
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

I know this subject has been tackled several times but I wasn't able to
find

[quoted text clipped - 17 lines]

Jeff