Get sheet names from a closed workbook without Opening it

In many occasions you would want to get the sheet names of a closed workbook without opening it to make your code run faster specially if that closed workbook is of a big size. So, here we go:


'Function that takes a workbook file name including the path
'and returns a 1 dimension array of its contained sheet names.
'Paracon Consultants Corp.

Function getSheetNamesFromClosedWorkbook(WorkbookFileName As String) As Variant

Dim oCon As Object
Dim oDb As Object
Dim oSh As Object
Dim sResult() As String
Dim i As Long

Set oCon = CreateObject("DAO.DBEngine.120")
Set oDb = oCon.OpenDatabase(WorkbookFileName, False, True, "Excel 12.0 Xml;HDR=Yes;")
'Redimensioning the Result array so it can take the sheet names.
ReDim sResult(1 To oDb.TableDefs.Count)

'Looping on each sheet (tabledef) object inside the DB object and getting its name

For Each oSh In oDb.TableDefs

i = i + 1
sResult(i) = oSh.Name
'To clean the sheet name and get it as how it is exactly in Excel, we have to remove some characters:
'1. All sheet names will have a $ sign at their end.
'2. if the sheet name has a space then it will be returned between single quotes.
'If there is a space in the sheet name then remove the first and last single quotes and the $ Sign
If sResult(i) Like "* *" Then

sResult(i) = VBA.Mid(sResult(i), 2, VBA.Len(sResult(i)) - 3)

'If there is no space then we need to remove only the $ sign form the end

sResult(i) = VBA.Left(sResult(i), VBA.Len(sResult(i)) - 1)

End If

Next oSh

getSheetNamesFromClosedWorkbook = sResult
Set oDb = Nothing
Set oCon = Nothing

End Function

Leave a comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.