none
Check if sheet exists in Excel ? RRS feed

  • Question

  • Hi, I would like check if particular sheets exist in an Excel file, I am using following code to read sheets to datatable but i want check if the sheets exists before. How can i do that?
            Try
    
                Dim MyConnection As OleDbConnection
                Dim MyCommand As OleDbDataAdapter            
                Dim path As String = "C:\temp\Sample.xlsx"
                Dim sheetz As Array = {"Sheet1", "Sheet2", "Sheet3"}
    
                MyConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
    
                For i = LBound(sheetz) To UBound(sheetz)
    
                    MyCommand = New OleDbDataAdapter("select * from [" & sheetz(i) & "$]", MyConnection)
                    Dim dtImport As DataTable = New DataTable()
                    MyCommand.Fill(dtImport)
                    MTO_DS.Tables.Add(dtImport)
    
                Next
    
                DataGridView1.DataSource = MTO_DS.Tables(0)
                DataGridView2.DataSource = GetTable() 'MTO_DS.Tables(1)
    
                MyConnection.Close()
    
                MTO_DS.Tables(0).Merge(MTO_DS.Tables(5))
    
            Catch ex As Exception
                MsgBox(ex.Message.ToString)
    
            End Try


    • Edited by Shan1986 Sunday, September 8, 2019 4:51 PM
    Sunday, September 8, 2019 4:51 PM

Answers

  • Hi,

    You can try my code, which will help you get all the sheet names in the specified Excel file.

    If you want to find out whether a specific sheet exists in an Excel file, just modify the Sub, pass in a String parameter name, that is the name of the sheet, and then compare whether the sheet name is equal each time you get it in the Sub.

    Public Class Form1
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            GetSheetName("D:\Student.xlsx")
        End Sub
        Public Sub GetSheetName(ByVal filepath As String)
            ListBox1.Items.Clear()
            Dim numberSheetID As Integer = 1
            Dim strSheetName As String = Nothing
            Dim SheetCount As Integer = 0
    
            If Not System.IO.File.Exists(filepath) Then
                MsgBox("This file is not exist")
            End If
    
            Try
                Dim obj As Microsoft.Office.Interop.Excel.Application = Nothing
                Dim objWB As Microsoft.Office.Interop.Excel.Workbook = Nothing
                obj = CType(Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", String.Empty), Microsoft.Office.Interop.Excel.Application)
                objWB = obj.Workbooks.Open(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
                SheetCount = objWB.Worksheets.Count()
                For numberSheetID = 1 To SheetCount
                    strSheetName = (CType(objWB.Worksheets(numberSheetID), Microsoft.Office.Interop.Excel.Worksheet)).Name
                    ListBox1.Items.Add("Sheet" & numberSheetID & " name:" & strSheetName)
                Next
                ListBox1.Items.Add("The number of sheets in " & filepath & " is:" & SheetCount)
                objWB.Close(Type.Missing, Type.Missing, Type.Missing)
                objWB = Nothing
                obj.Quit()
                obj = Nothing
    
            Catch Err As Exception
                MsgBox(Err.Message)
            End Try
        End Sub
    End Class
    

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Shan1986 Monday, September 9, 2019 9:45 AM
    Monday, September 9, 2019 8:53 AM

All replies

  • Hi , 

    Check this threat i think it is useful

    Sheet Excel

    And this snippet for check if the file is open or not  

    Private Function FileInUse(ByVal path As String) As Boolean
            Try
    
                Using fs As FileStream = New FileStream(path, FileMode.OpenOrCreate)
                End Using
    
                Return False
            Catch ex As IOException
                Return True
            End Try
        End Function
    
     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            If FileInUse("F:\test.docx") = True Then
                MsgBox("file is open you can't use it")
                Exit Sub
            End If
            MsgBox("file is closed you can use it")
        End Sub


    Regards From Egypt

    Sunday, September 8, 2019 11:16 PM
  • When you execute ‘MyCommand.Fill(dtImport)’, do you observe any specific exception? If so, then you can use a Try-Catch block around this fragment in order to detect this kind of failures, skip ‘MTO_DS.Tables.Add’, etc.

    Monday, September 9, 2019 5:17 AM
  • Hi , 

    Check this threat i think it is useful

    Sheet Excel

    And this snippet for check if the file is open or not  

    Private Function FileInUse(ByVal path As String) As Boolean
            Try
    
                Using fs As FileStream = New FileStream(path, FileMode.OpenOrCreate)
                End Using
    
                Return False
            Catch ex As IOException
                Return True
            End Try
        End Function
    
     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            If FileInUse("F:\test.docx") = True Then
                MsgBox("file is open you can't use it")
                Exit Sub
            End If
            MsgBox("file is closed you can use it")
        End Sub


    Regards From Egypt

    Hi, I want to check if specif sheet exists in an excel file. NOT if the file is open or not. Anyway thanks.
    Monday, September 9, 2019 7:14 AM
  • When you execute ‘MyCommand.Fill(dtImport)’, do you observe any specific exception? If so, then you can use a Try-Catch block around this fragment in order to detect this kind of failures, skip ‘MTO_DS.Tables.Add’, etc.

    Thanks . I will add try and catch. If sheet exists i dont get any exception. Thats why i want to check for specific sheet and alert the user that its missing. 
    Monday, September 9, 2019 7:15 AM

  • Hi, I want to check if specif sheet exists in an excel file. NOT if the file is open or not. Anyway thanks.

    Hi,

    I thought if you go to the link of Alex answer (Sheet Excel), it may be guide you to solve your issue 

    Anyway I want to help you only


    Regards From Egypt

    Monday, September 9, 2019 8:22 AM
  • Hi,

    You can try my code, which will help you get all the sheet names in the specified Excel file.

    If you want to find out whether a specific sheet exists in an Excel file, just modify the Sub, pass in a String parameter name, that is the name of the sheet, and then compare whether the sheet name is equal each time you get it in the Sub.

    Public Class Form1
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            GetSheetName("D:\Student.xlsx")
        End Sub
        Public Sub GetSheetName(ByVal filepath As String)
            ListBox1.Items.Clear()
            Dim numberSheetID As Integer = 1
            Dim strSheetName As String = Nothing
            Dim SheetCount As Integer = 0
    
            If Not System.IO.File.Exists(filepath) Then
                MsgBox("This file is not exist")
            End If
    
            Try
                Dim obj As Microsoft.Office.Interop.Excel.Application = Nothing
                Dim objWB As Microsoft.Office.Interop.Excel.Workbook = Nothing
                obj = CType(Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", String.Empty), Microsoft.Office.Interop.Excel.Application)
                objWB = obj.Workbooks.Open(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
                SheetCount = objWB.Worksheets.Count()
                For numberSheetID = 1 To SheetCount
                    strSheetName = (CType(objWB.Worksheets(numberSheetID), Microsoft.Office.Interop.Excel.Worksheet)).Name
                    ListBox1.Items.Add("Sheet" & numberSheetID & " name:" & strSheetName)
                Next
                ListBox1.Items.Add("The number of sheets in " & filepath & " is:" & SheetCount)
                objWB.Close(Type.Missing, Type.Missing, Type.Missing)
                objWB = Nothing
                obj.Quit()
                obj = Nothing
    
            Catch Err As Exception
                MsgBox(Err.Message)
            End Try
        End Sub
    End Class
    

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Shan1986 Monday, September 9, 2019 9:45 AM
    Monday, September 9, 2019 8:53 AM