Export to a specific worksheet using transferspreadsheet

[neeko1226]

Is it possible to export to a specific worksheet in Excel and overwrite the existing data using the transferspreadsheet method in VBA? I need to export a query to a worksheet that holds the raw data in an Excel workbook. There are several other worksheets in the workbook that connected to the "data" worksheet. Right now this code is creating a new worksheet (data1) in the workbook.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_CDL_Sum_Test", "L:\Riskman\Mock Exam\CDL Summary\CD_Loan_Summary.xls", True, "Data"

Any help would be greatly appreciated.

 

[PHV]

You may try this:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_CDL_Sum_Test", "L:\Riskman\Mock Exam\CDL Summary\CD_Loan_Summary.xls", True, "Data$"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[neeko1226]

Thank you for the reply PHV, but I guess it's not all about the $ here. I added the the $ and received the following error message:

"Run-time error '3010'
Table 'Data$' already exists."

I've been searching the net for a solution and it looks like some people are activating the worksheet, clearing it out and then repopulating it with VBA code. The examples our on the web are very complex and beyond my capabilities. Does anyone have a simple version that I could build off of? I know how to create a recordset and loop through it, but I don't know how to assign values from the recordset to another source.