Member Avatar for cguan_77

Hi guys, is there a way to get worksheet names from a close workbook? thanks for any help.

  • 2 Contributors
  • 2 Replies
  • 261 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by cguan_77

Dani AI

Generated

’s Interop solution is fine when you’re on a desktop with Excel installed, but there are cleaner/no-Excel options if you just need the sheet names (and you want something safe for automation or servers). Microsoft recommends avoiding Office COM automation for unattended/server code — prefer Open XML or a file-only library instead. (support.microsoft.com)

A simple, reliable approach for .xlsx files is to read the workbook part with the Open XML SDK (no Excel install required). Example (VB.NET):

Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Public Function GetSheetNamesFromXlsx(filePath As String) As List(Of String)
    Dim names As New List(Of String)
    Using doc = SpreadsheetDocument.Open(filePath, False)
        Dim sheets = doc.WorkbookPart?.Workbook?.GetFirstChild(Of Sheets)()
        If sheets IsNot Nothing Then
            For Each s As Sheet In sheets.Elements(Of Sheet)()
                names.Add(s.Name.Value)
            Next
        End If
    End Using
    Return names
End Function

Open XML docs and examples explain the Sheets collection and attributes. (learn.microsoft.com)

If you need to handle older .xls files (or want a quick ADO.NET route), you can use the OLE DB provider and GetOleDbSchemaTable to list "TABLE" entries (works like a schema query). Watch out: provider/bitness mismatches, the ACE/JET driver must be present, the returned names often include a trailing $ and named ranges may appear — filter by TABLE_TYPE and trim the $. Also OLE DB may not preserve the visual sheet order. (ftp.zx.net.nz)

If you prefer a high-level API, libraries like ClosedXML, EPPlus or NPOI make iterating worksheets trivial and are suitable for server use (no Excel COM). For most server or automation scenarios pick Open XML or a maintained library rather than Excel interop. (docs.closedxml.io)

Troubleshooting quick notes: match process bitness to ACE/JET, filter schema rows by TABLE_TYPE = "TABLE", and remember hidden sheets may not be visible via OLE DB (use a file-library or Interop if you must detect visibility).

Recommended Answers

All 2 Replies

Member Avatar for Reverend Jim

I presume you mean an Excel Workbook/Worksheet. As far as I know you have to open it first but you can do that easily with the following function

Private Function GetWorkSheetNames(ByVal ExcelFile As String) As String()

    Dim names As String = ""
    Dim xls As New Excel.Application
    xls.Workbooks.Open(ExcelFile)

    For Each sheet As Excel.Worksheet In xls.ActiveWorkbook.Worksheets
        names = names & vbCr & sheet.Name
    Next

    xls.Quit()

    Return names.Substring(1).Split(vbCr)

End Function
commented: Thanks. +8
Member Avatar for cguan_77

Thanks Jim :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.