Deleting one excel worksheet from Access VBA

[BillDickenson]

I am updating an excel spreadheet from access. Before I export the query, I need to delete the worksheet thats already there. I seem to get sporadic responses to this code. Can someone point out what I'm missing ?

I'm getting a 438 error. Thanks

Function KillExcelSheet(szFileName As String, szSheetName As String) As Boolean

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim index As Integer
Dim szW1Name As String
Dim szW2Name As String


On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If TypeName(xlApp) = "Nothing" Then
'Excel was not open
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0

Set xlWB = xlApp.WorkBooks.Open(szFileName)

Call Bark("Killing " & szSheetName & " from" & szFileName, 2)


For index = 1 To xlWB.Worksheets.Count

szW1Name = Left(xlWB.Worksheets(index).Name, 3)
szW2Name = Left(szSheetName, 3)
'MsgBox szW1Name & ":" & szW2Name
'szW1Name = szW2Name

If szW1Name = szW2Name Then
MsgBox "Deleting " & xlWB.Worksheets(index).Name
'This line is generating a 438 error
xlWB.Worksheets(index).Remove
End If
Next index

With xlWB
.Save
.Close
End With

End Function

 

[PHV]

Replace this:
For index = 1 To xlWB.Worksheets.Count
By this:
For index = xlWB.Worksheets.Count To 1 Step -1
And this:
xlWB.Worksheets(index).Remove
By this:
xlWB.Worksheets(index).Delete

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BillDickenson]

Arrgg. I feel stupid. Thanks. That worked perfectly. Thanks for the speedy answer.

 

[BillDickenson]

Hate to open this back up, but the sheet is not getting deleted. Not sure why. Any thoughts ?

 

[PHV]

A workbook must have at least one sheet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BillDickenson]

It does. Actually, it has 1 visible and 3 hidden. Oddly enough, it seems to delete the first sheet I find, but not the second. I may be making this harder on myself than I have to. Really, I just want to have 1 of each of these in the excel file. Here is the calling code:

Public Function bldDetailReport(szFileName As String)

Dim pathname As String
Dim szDTLQueryName As String
Dim szEmpQueryName As String

szDTLQueryName = "Detail"
szEmpQueryName = "Employee"

Call KillExcelSheet(szFileName, szDTLQueryName)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, szDTLQueryName, szFileName
Call KillExcelSheet(szFileName, szEmpQueryName)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, szEmpQueryName, szFileName

End Function

 

[PHV]

Doing TransferSpreadsheet while Excel is running is, in my opinion, unsafe.
You may try this revised version of your function.

Function KillExcelSheet(szFileName As String, szSheetName As String) As Boolean
 Dim xlApp As Object, xlWB As Object
 Dim index As Integer

 Set xlApp = CreateObject("Excel.Application")
 Set xlWB = xlApp.WorkBooks.Open(szFileName)
 Call Bark("Killing " & szSheetName & " from" & szFileName, 2)
 With xlWB
   For index = .Worksheets.Count To 1 Step -1
     If UCase(Left(.Worksheets(index).Name, 3)) = UCase(Left(szSheetName, 3)) Then
       MsgBox "Deleting " & .Worksheets(index).Name
       .Worksheets(index).Delete
     End If
   Next index
   .Save
   .Close
 End With
 Set xlWB = Nothing
 xlApp.Quit
 Set xlApp = Nothing        
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BillDickenson]

I'll try that. In addition to this, do you think I should delete both sheets first, then add both ? That way the number of opens would be reduced.