Delete Worksheets Prior To Access DoCmd.TransferSpreadsheet
 

Hi All,

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

I can access the Excel workbook fine from Access. I can manipulat
worksheets (hide or change Cell values) fine, but I am not able t
delete worksheets. It seems to process the code, but the delete par
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 delet
existing
' worksheets as needed.
For Each wks In wkb.Worksheets
' Check if the worksheet is an export from Access (prefix o
"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, tha
part works alright. I'm just trying to delete or overwrite the existin
"qxls" worksheets.

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

Thanks

--
wheatcracke
-----------------------------------------------------------------------
wheatcracker's Profile: http://www.excelforum.com/member.php...fo&userid=3205
View this thread: http://www.excelforum.com/showthread.php?threadid=51806