Get sheet names from a closed workbook without Opening it

'Function that takes a workbook file name including the path
'and returns a 1 dimension array of its contained sheet names.
'Paracon Consultants Corp. https://paracon.ca
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
Else

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

End If

Next oSh

getSheetNamesFromClosedWorkbook = sResult
oDb.Close
Set oDb = Nothing
Set oCon = Nothing

End Function