Hi All,

I know this is an Excel group, but it's an Excel related problem. I am using the DoCmd.TransferSpreadsheet command in Access (2000) to transfer query results to an Excel workbook. It transfers fine, but the problem I need to address is that I the transfer creates copies of the worksheet if one already exists. (e.g., transfer "qxlsTest" creates a worksheet named "qxlsTest" if one does not exist, but creates "qxlsTest1" if one already does.) I need to delete or overwrite the existing "qxlsTest" worksheet.

I can access the Excel workbook fine from Access. I can manipulate worksheets (hide or change Cell values) fine, but I am not able to delete worksheets. It seems to process the code, but the delete part doesn't actually happen.

Here's (part of) the code I'm using:
(strFilename is the filename passed to this function)

Dim xlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet

Set xlApp = CreateObject("Excel.Application")
Set wkb = xlApp.Workbooks.Add(strFilename)

xlApp.DisplayAlerts = False

' Iterate through all the worksheets in the workbook and delete existing
' worksheets as needed.
For Each wks In wkb.Worksheets
' Check if the worksheet is an export from Access (prefix of "qxls").
If Left(wks.Name, 4) = "qxls" Then
' Delete the current worksheet.
wks.Delete
End If
Next wks

After the code above, the DoCmd.TransferSpreadsheet occurs. Again, that part works alright. I'm just trying to delete or overwrite the existing "qxls" worksheets.

Any help is greatly appreciated. I'm going nuts.

Thanks!