Getting worksheet names from closed workbook: too many names returned

[EdNerd]

I am working with XLSM files that for unknown reasons are crashing Excel when certain users open them. The template for these workbooks was originally created in XL2007, and has been tweaked and added to over the years. We are now using Office 365.

I have code to obtain data from the workbooks without opening the files, but that code depends on having the name of the worksheet. I found code to get the worksheet names here: Search file . Unfortunately, that code returns 'way too many names! The workbook I'm testing on has 31 worksheets, but I'm returning over 4900 names!! It looks like there's a "worksheet name" generated for each named range and filtered name, plus internal XL constants (like PrintRange and such) for each worksheet.

If I unzip the file, I see the xl/worksheets/ directory has only the 31 sheets called out as XML files. Even so, the XML only has the worksheet "codeName" ("Sheet13") - the tab label is nowhere to be found in the entire XML.

Is there a reliable method to extract only the worksheet tab labels from a file? Or to use the referenced code, but filter out everything that is not a valid sheet tab name?

 

[RoryA]

Using the ADO code, you can check if the 'table' name ends in $ or $' and if it does, it's a worksheet.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

UtterAccess.com

www.utteraccess.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[EdNerd]

Using the ADO code, you can check if the 'table' name ends in $ or $' and if it does, it's a worksheet.

Rory: As I look at the array of returned names, using the "$" marker works for most of the sheet names. Six would be left out and not captured, though.

 

[RoryA]

There should not be any worksheets that don’t have a $ at the end of the name.

 

[JEC]

Try this function. Ref parameter needs a sheet index.

Sub wsName()
Debug.Print GetSheetName("C:\Users\xxx\Downloads\blabla.xlsm", 1)
End Sub

Function GetSheetName(fn As String, ref As Long) As String
    With CreateObject("DAO.DBEngine.120").OpenDatabase(fn, False, False, "excel 5.0;hdr=no;")
        GetSheetName = Replace(Replace(.tabledefs(ref - 1).Name, "$", ""), "'", "")
    End With
End Function

 

[JEC]

Just to be clear. This for all sheets

Sub wsName()
GetSheetNames "C:\Users\xxx\Downloads\blabla.xlsm"
End Sub

Function GetSheetNames(fn As String) As String
    With CreateObject("DAO.DBEngine.120").OpenDatabase(fn, False, False, "excel 5.0;hdr=no;")
      For Each sh In .tabledefs
        Debug.Print sh.Name
      Next
    End With
End Function

 

[EdNerd]

Rory: Rule #13 is understood.

 

[EdNerd]

Just to be clear. This for all sheets

Sub wsName()
GetSheetNames "C:\Users\xxx\Downloads\blabla.xlsm"
End Sub

Function GetSheetNames(fn As String) As String
    With CreateObject("DAO.DBEngine.120").OpenDatabase(fn, False, False, "excel 5.0;hdr=no;")
      For Each sh In .tabledefs
        Debug.Print sh.Name
      Next
    End With
End Function

This worked!! All of the valid sheet names end in $, although many are also surrounded in single quotes. So: I process everything that ends in $ as a valid sheet name, and use string functions to remove the ' and $, and I have my list. Thank you!!!!

 

[JEC]

great! Cheers!