Getting worksheet names from closed workbook: too many names returned

EdNerd

Active Member
Joined
May 19, 2011
Messages
464
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Using the ADO code, you can check if the 'table' name ends in $ or $' and if it does, it's a worksheet.
 
Upvote 0
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:
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.
 
Upvote 0
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.
 
Upvote 0
There should not be any worksheets that don’t have a $ at the end of the name.
 
Upvote 0
Try this function. Ref parameter needs a sheet index.

VBA Code:
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
 
Upvote 0
Just to be clear. This for all sheets

VBA Code:
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
 
Upvote 0
Solution
Just to be clear. This for all sheets

VBA Code:
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!!!!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top