Deleting sheets

[cassidyr1]

Hello,

I am using vb6 program (not vba) and using the excel object. I have
template that contains some data sheets that data gets written to. No
all of these get used, and at the end I loop through the sheets to d
some other stuff, and I want to check if any of these redundant sheet
are left, and then delete them. Here is the relevant bit of code i
the loop:-

If InStr(export.Worksheets(i).Name, "DATA_SHEET") Then
Application.DisplayAlerts = False
export.Worksheets(i).Visible = True
export.Worksheets(i).Delete
Application.DisplayAlerts = True
End If

It unhides all of these DATA_SHEET sheets, but doesn't delete them, I'
at a loss as if I put this code into a vba module it deletes them! An
help would be greatly appreciated.

Thanks

Richar

 

[XL-Dennis]

Hi Richard,

Below You find an approach that seems to do the work:


Code
-------------------

Option Explicit

Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlwbBook As Excel.Workbook
Dim xlwsSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlwbBook = xlApp.Workbooks.Open("c:\Test.xls")

xlApp.ScreenUpdating = False

'......

For Each xlwsSheet In xlwbBook.Worksheets
If InStr(xlwsSheet.Name, "DATA_SHEET") Then
With xlApp
.DisplayAlerts = False
With xlwsSheet
.Visible = True
.Delete
End With
End With
End If
Next xlwsSheet

'.....

With xlwbBook
.Save
.Close
End With

With xlApp
.DisplayAlerts = True
.ScreenUpdating = True
.Quit
End With

Set xlwsSheet = Nothing
Set xlwbBook = Nothing
Set xlApp = Nothing

End Sub

-------------------



Kind regards,
Denni

 

[XL-Dennis]

A slight modification of the code...:


Code
-------------------

With xlApp
.ScreenUpdating = False
.DisplayAlerts = False
End With

'......

For Each xlwsSheet In xlwbBook.Worksheets
If InStr(xlwsSheet.Name, "DATA_SHEET") Then
With xlwsSheet
.Visible = True
.Delete
End With
End If
Next xlwsSheet

'......

 

[cassidyr1]

Thanks for the help, but that is effectively the same was as I hav
done, except referencing the worksheet in a different way. I tried b
creating the worksheet object and looping through the worksheet
collection ( as you suggested), but still no joy - I can't understan
why this isn't working!