[RESOLVED] Get Worksheet Name from a close Workbook

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

thanks.

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.

Code:

---

<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.

Code:

---

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

---

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

Quote:

---

Originally Posted by 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

---

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.

Code:

---

    <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

---

Thanks Kevin, appreciate your help. :)