Error '3010' using TransferSpreadsheet

[BMeek]

I have an Access program that outputs data into an Excel Spreadsheet that creates a one-page report with two charts. I am using DoCmd.TransferSpreadsheet to export the a query results as static data to an Excel template file. The query is exported as a separate worksheet into the workbook template file. The problem is, later in the process I have to open the Excel file and go through a series of updates to other items in the file, then print the report and close Excel. All this is done behind the scenes and the user never sees Excel open.

In case the user wants to run another report with a slight variation in parameters, when the "Print Report" button is clicked again I get a runtime error '3010'- Table 'TableName' already exists from the TransferSpreadsheet function. No matter what I try, there is still an instance of Excel open and I get the error message when the static transfer finds the file open. What I want to do is create an error trap and overwrite the table. Do I need to delete the worksheet before coming back to Access or is there another way to force Access to overwrite the "table"?

Here is the code from the TransferSpreadsheet module:
Sub ExportZipDataToExcel(strTableName, strFileName As String, StrRange As String, blnHasFieldNames As Boolean)

' Exports a table or query as static data to Excel.
strTableName = "PWR_MarketTrendZIP"
strFileName = "C:\PWRSolution\ZipMarketAssess.xls"
StrRange = "PWR_MarketTrendZIP"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTableName, strFileName, blnHasFieldNames

End Sub
Here is the code from a module that opens the Excel file and attempts to close out the instance of Excel:
Sub GetObjectZipXL()
Dim xlApp As Excel.Application
Dim blnUserControl As Boolean
Dim xlFile As String

Const ERR_APP_NOTRUNNING As Long = 429

' Set blnUserControl to True as default.
blnUserControl = True
On Error Resume Next
' Attempt to open current instance of Excel.
Set xlApp = GetObject(, Excel.Application)
' If no instance, create new instance.
If Err = ERR_APP_NOTRUNNING Then
Set xlApp = New Excel.Application
' Store current state of UserControl property.
blnUserControl = xlApp.UserControl
xlFile = "C:\PWRSolution\ZipMarketAssess.xls"
End If

With xlApp
' Code to automate Excel here.
.Workbooks.Open xlFile
Call GetZipData
End With

xlApp.ActiveWorkbook.Close SaveChanges, "C:\PWRSolution\ZipMarketAsses.xls"

' Check original value of UserControl property.
If blnUserControl = False Then
xlApp.Quit
Set xlApp = Nothing
End If

End Sub

Any suggestions are greatly appreciated! If I need to change one of the ways I am outputting the data to Excel, so be it!

Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A "Solution Provider" in your corner gives you the personal attention you need to find the right technology solutions for your business.