Delete Excel Sheets With Name Criteria

[meho2020]

Dear All

I have a code to delete excel sheets having a common Criteria in their Names Like T10, T12, T16 and so on
I want the code to delete these sheets, the code is working on VBA but it does not on vb.net ??

My Code is
g

 Private Sub DeleteTSheets()
        Dim xl As New Excel.Application
        Dim wb As Excel.Workbook
        Dim ws as Excel.Worksheet
        wb = xl.Workbooks.Open("C:\Patches\Main_Final.xlsm")
        For Each ws in wb.sheets
           If InStr(1, wb.Sheets(i).Name, "T") Then
              If len(ws.name)=3 then
                   ws.delete()
              End If
           End If
        Next
       End Sub

So, What is wrong exactly?

Thanks, Regards

Moheb Labib

[ChrisE]

I doubt (even in VBA) that it will delete the Sheet while you are searching in it.

break it up into more actions
a) search the folder for excel workbooks and return taht to a listbox
b) loop with the File name and return each sheet
c) perform your search with that sheet name and return the cell Range where it was found
d) then decide if you really want to delete that sheet

here a start to get the sheet names

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        GetExcelSheetNames("E:\A9.xlsx")
    End Sub

    Private Sub GetExcelSheetNames(ByVal fileName As String)
        Dim strconn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
              fileName & ";Extended Properties=""Excel 12.0 Xml;"""
        Dim conn As New OleDb.OleDbConnection(strconn)

        conn.Open()

        Dim dtSheets As DataTable =
                  conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        Dim listSheet As New List(Of String)
        Dim drSheet As DataRow

        For Each drSheet In dtSheets.Rows
            listSheet.Add(drSheet("TABLE_NAME").ToString())
        Next

        ListBox1.DataSource = listSheet.ToArray
        conn.Close()

    End Sub

[Delaney]

Hello,

The command InStr is misused. The result is the position of what your are looking for. In your case the letter T at the first place. the string to use is ws.name not
wb.Sheets(i).Name as you already loop through the sheets with the For Each.

 Dim xl As New Excel.Application
        Dim wb As Excel.Workbook
        Dim ws As Excel.Worksheet
        wb = xl.Workbooks.Open("C:\Patches\Main_Final.xlsm")
        For Each ws In wb.Sheets
            If InStr(1, ws.Name, "T") = 1 Then
                If Len(ws.Name) = 3 Then
                    ws.Delete()
                End If
            End If
        Next

I Tested it , it's working
regards

PS : seems, I am slow to answer

[ChrisE]

I Tested it , it's working
regards

PS : seems, I am slow to answer

Hi Delany,
did it really delete the sheet while you were searching for a value ?

[Delaney]

No, It deletes the sheet starting by T (and with 3 characters) when the workbook is closed. Th OP didn't precise that it was while searching for a value . I just corrected the code he put.

[ChrisE]

I thought that
I think in VBA you have to put a EXIT For
to stop the Loop and then you can Delete, but I'm not sure, and to lazy to test that

[Delaney]

if the workbook is already open, the code would be a bit different as you should not create a new instance. You would use

 Dim XlApp As Object = CreateObject("excel.application")

And you will have to save the workbook with a new name but the new workbook won't have the deleted sheets

[meho2020]

Dear Delaney
Thanks your support, However the code is working as vba on excel, I want it to work on VB.net, it seems to me that the excel file may be opened in the background or read only and it won't let the code to work on VB.net, I know that it is working on VBA
Thanks, Regards

[meho2020]

Dear All,

I found The Solution, The Code is correct but it is worked only when I removed all macros from the excel sheet so what is this means??

The Correct Code is

 Private Sub DeleteTSheets()
        Dim xl As New Excel.Application
        Dim wb As Excel.Workbook
        Dim ws as Excel.Worksheet
        wb = xl.Workbooks.Open("C:\Patches\Main_Final.xlsm")
        For Each ws in wb.sheets
           If InStr(1, ws.Name, "T") Then
              If len(ws.name)=3 then
                   ws.delete()
              End If
           End If
        Next
       wb.Close(Savechanges:=vbTrue)
       End Sub

[Delaney]

I wonder.. if you have Option Strict= On this should raise an error as it does for me:

If InStr(1, ws.Name, "T") Then

or I missed something