My Blog

Checking if a Sheet Exists

In continuation from here

Sometimes you need to know if a particular sheet is there in Excel. I am showing you two ways to check that. the first way is to directly check for the existence of that sheet and the second way is to create a function so that you can use it again and again if required. For this example we will be using the .Open Method to open an existing workbook and then checking for the sheets in that workbook. Lets say we have a workbook “Sample.xlsx” which looks like this

And we are supposed to check if the workbook has a sheet called “Sample”

Way 1

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim SheetNameToCheck As String = "Sample"
    Dim xs As Excel.Worksheet

    '~~> Opens an exisiting Workbook. Change path and filename as applicable
    xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.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
            MessageBox.Show("The sheet " & SheetNameToCheck & " found.")
        End If
    Next
End Sub

Way 2

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    '~~> Define your Excel Objects 
    Dim xlApp As New Excel.Application 
    Dim xlWorkBook As Excel.Workbook 
    Dim xlWorkSheet As Excel.Worksheet 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 
    End Sub 

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 
    Dim SheetNameToCheck As String = "Sample" '~~> Opens an exisiting Workbook. Change path and filename as applicable

        '~~> Define your Excel Objects Dim xlApp As New Excel.Application 
        Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet 
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel xlApp.Visible = True '~~> Check If sheet exists using a function
        If DoesSheetExists(SheetNameToCheck) Then
            MessageBox.Show("The sheet " & SheetNameToCheck & " found.")
        Else
            MessageBox.Show("The sheet " & SheetNameToCheck & " not found.")
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

    Public Function DoesSheetExists(ByVal shtName As String) As Boolean
        Dim xs As Excel.Worksheet

        DoesSheetExists = False

        '~~> Loop through the all the sheets in the workbook to find if name matches
        For Each xs In xlWorkBook.Sheets
            If xs.Name = shtName Then
                DoesSheetExists = True
            End If
        Next
    End Function
End Class

The above function will be helpful if you are planning to check the names of many sheets. This way you will not have to write the code for looping and checking that many times.