[RESOLVED] Get Worksheet Name from a close Workbook

[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