[RESOLVED] Get Worksheet Name from a close Workbook

[JJJCR_FOX]

hi guys, how to get worksheet names from a close workbook, in vb.net?

thanks.

[kareninstructor]

Using OleDb but the sheet names will be sorted A-Z not ordinal as seen in the file. Call the extension method on a connection setup to open an Excel file and make sure the connection is open first.

<Runtime.CompilerServices.Extension()> _
Public Function ExcelTables(ByVal sender As OleDbConnection) As List(Of String)
    Dim dt As DataTable = sender.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() _
                                         {Nothing, Nothing, Nothing, "TABLE"})
    Return (From T In dt.AsEnumerable Select T.Field(Of String)("Table_Name")).ToList
End Function

For early binding this should do the trick. I believe each sheet is not opened unless you invoke Activate method of the sheet which is not done here. The sheet names are shown in their normal ordinal positions.

If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

    Dim List As New List(Of String)
    Dim App As Excel.Application
    Dim WorkBook1 As Excel.Workbook
    Dim Sheet1 As Excel.Worksheet = Nothing

    App = New Excel.Application
    WorkBook1 = App.Workbooks.Open(OpenFileDialog1.FileName)

    For x As Integer = 1 To WorkBook1.Sheets.Count
        Sheet1 = CType(WorkBook1.Sheets(x), Excel.Worksheet)
        List.Add(Sheet1.Name)
    Next

    WorkBook1.Close()

    App.UserControl = True
    App.Quit()

    If Not Sheet1 Is Nothing Then
        Runtime.InteropServices.Marshal.FinalReleaseComObject(Sheet1)
        Sheet1 = Nothing
    End If

    If Not WorkBook1 Is Nothing Then
        Runtime.InteropServices.Marshal.FinalReleaseComObject(WorkBook1)
        WorkBook1 = Nothing
    End If

    If Not App Is Nothing Then
        Runtime.InteropServices.Marshal.FinalReleaseComObject(App)
        App = Nothing
    End If

    GC.WaitForPendingFinalizers()
    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()

End If

[JJJCR_FOX]

hi kevin, sorry for being so Noob.
but i'm getting error like excel.application, excel.workbook is not defined, OleDBConnection is not Defined

i had tried to add: Imports Microsoft.Office.Core but it's getting error as well

i'm using VS2010 Express, thanks for your help

[kareninstructor]

hi kevin, sorry for being so Noob.
but i'm getting error like excel.application, excel.workbook is not defined, OleDBConnection is not Defined

i had tried to add: Imports Microsoft.Office.Core but it's getting error as well

i'm using VS2010 Express, thanks for your help

Here is a VS2008 version which when opened in VS2010 it will be converted. For your project add a Reference to Microsoft.Office.Interop.Excel

http://kevininstructor.home.comcast....amedRanges.zip

There is also a extension method via OleDb to get Named Ranges from Excel files.

    <Runtime.CompilerServices.Extension()> _
    Public Function NamedRanges(ByVal sender As OleDbConnection) As List(Of String)
        Return (From T In ExcelTables(sender) Where Not T.Contains("$")).ToList
    End Function

[JJJCR_FOX]

Thanks Kevin, appreciate your help.