CODEWITHSUNDEEP
vb.netexceloffice-interop
Ahmed Faizan
Ahmed Faizan

Reputation: 456

Check to see if Sheet exists in Excel and record result as Boolean

I am learninf excel to vb.net connection from this site: http://www.siddharthrout.com/2012/09/09/checking-if-a-sheet-exists/

I am trying to check if sheet exists and record result in boolean. 

    Dim SheetNameToCheck As String = "Sheet1"
    Dim xs As Excel.Worksheet
    Dim sheet_found As Boolean
    '~~> Opens an exisiting Workbook. Change path and filename as applicable
    xlWorkBook = xlApp.Workbooks.Open("C:\...\myExcel2007file.xlsx")
    '~~> Display Excel
    xlApp.Visible = True
    '~~> Loop through the all the sheets in the workbook to find if name matches
    For Each xs In xlWorkBook.Sheets
        If xs.Name = SheetNameToCheck Then
            sheet_found = True
        Else
            sheet_found = False
        End If
    Next



    If sheet_found = True Then
        MessageBox.Show("The sheet " & SheetNameToCheck & " found.")
    Else
        MessageBox.Show("Not found.")
    End If

The problem is that the result comes as Not Found whatever string you check with

The error comes in the For loop. First the loop checks excel Sheet1 to see if it meets the string to check which is "Sheet1". The variable sheet_found is obviously "True".

But when it goes to the next sheet, Sheet 2 and Sheet3 the result turns to false and I an unable to check if the sheet actually exists in the workbook.

Upvotes: 1

Views: 11090

Answers (3)

Akos
Akos

Reputation: 1

Why bother with that else statement at the first place? Make it false as default, and only change it to true, if you find your sheet.

Upvotes: 0

Ahmed Faizan
Ahmed Faizan

Reputation: 456

Thanks to varocarbas and patrick for the answers. Here is the code that worked

 Dim SheetNameToCheck As String = "Sheet22"
    Dim xs As Excel.Worksheet
    Dim sheet_found As Boolean
    '~~> Opens an exisiting Workbook. Change path and filename as applicable
    xlWorkBook = xlApp.Workbooks.Open("C:\...\myExcel2007file.xlsx")
    '~~> Display Excel
    xlApp.Visible = True
    '~~> Loop through the all the sheets in the workbook to find if name matches

    For Each xs In xlWorkBook.Sheets
        If xs.Name = SheetNameToCheck Then
            sheet_found = True
            Exit For
        Else
            sheet_found = False
        End If
    Next

    If sheet_found = True Then
        MessageBox.Show("The sheet " & SheetNameToCheck & " found.")
    Else
        MessageBox.Show("Not found.")
    End If

Upvotes: 0

Patrick
Patrick

Reputation: 476

under sheet_found = True should be an Exit For line before the Else statement, as varocarbas suggests

Upvotes: 1

Related Questions