Get sheet name of closed workbook

inwin

New Member
Joined
Jan 11, 2006
Messages
9
Is it possible to get names of sheets of a workbook without using Workbooks.Open

I need to find the specified worksheet within the number of workbooks in the folder and it takes a lot of time to open each file...
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Not using normal Excel features, no.

Why is it taking a lot of time to open these ... are they very big?
 
Upvote 0
1. They are stored on a network drive.
2. Average size is about 2mb.
3. The number of files is about 100.
4. I plan to add found sheets to combobox and reopen some of them later and it will take too much time in total and seems not to be smart...
 
Upvote 0
Well, in my opinion you'd be better off just opening the workbooks in code, do the sheet name population in your combobox programmatically, and close the workbooks again, which could be done in a split second of macro execution, where the user is none the wiser, at least not visually.

That said, your request is possible using an ADO connection. First, go into the VBE and establish references to:

Microsoft ActiveX Data Objects 2.7 Library
and
Microsoft DAO 3.6 Object Library

Note, your specific Library versions may differ slightly, depending on your version of Excel. I am using Excel2003 and the above versions are available in the References dialog for my version.

Next, stick this function in a standard vba module:


Function GetSheetsNames(WBName As String) As Collection
Dim objConn As ADODB.Connection
Dim objCat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim sConnString As String
Dim sSheet As String
Dim Col As New Collection

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WBName & ";" & _
"Extended Properties=Excel 8.0;"

Set objConn = New ADODB.Connection
objConn.Open sConnString
Set objCat = New ADOX.Catalog
Set objCat.ActiveConnection = objConn

For Each tbl In objCat.Tables
sSheet = tbl.Name
sSheet = Application.Substitute(sSheet, "'", "")
sSheet = Left(sSheet, InStr(1, sSheet, "$", 1) - 1)
On Error Resume Next
Col.Add sSheet, sSheet
On Error GoTo 0
Next tbl
Set GetSheetsNames = Col
objConn.Close
Set objCat = Nothing
Set objConn = Nothing
End Function



Finally, assuming your combobox is named ComboBox1 and is in a userform, stick this in your userform module:



Private Sub UserForm_Initialize()
Application.ScreenUpdating = False

Dim Col As Collection, Book As String, i As Long
Book = "C:\Your\File\Path\YourFileName.xls"
Set Col = GetSheetsNames(Book)

With ComboBox1
.Clear
For i = 1 To Col.Count
.AddItem Col(i)
Next i
.ListIndex = 0 'Optional
End With

Application.ScreenUpdating = True
End Sub



The essence of the code (especially the function) is from a post I saw by Juan Pablo Gonzales a couple years ago. I only modified it a little to fit your circumstance here, and it tested fine on my system, so thanks to Juan for this.

From this point, simply modify the combobox population for the path and closed workbook(s) you want to identify sheet names for.
 
Upvote 0
Well, I got
Compile error:
User-defined type not defined
And the string "Dim objCat As ADOX.Catalog" is highlighted...

smth is wrong? By the way I don't have any idea of what ADOX.Catalog is..))
P.s. I refered Microsoft ActiveX Data Objects 2.7 Library
Microsoft DAO 3.6 Object Library as you told me to do (I found versions 2.7 and 3.6 on my excel too)
 
Upvote 0
Too darned many of those ref libs to keep track of...you also need, though I did not specify so apologies, to establish a reference to
"Microsoft ADO Ext. 2.8 for DDL and Security".

After that try the code again, I get no errors with those refs established for this one.


Edit, I see you found it on your own as I composed the response, good job of researching !!
 
Upvote 0
Great! It works! And fast enough! I tested in on simple xls-file with 3 sheets...

But unfortunately it doesn't work with workbooks with a large number of sheets(about 50-100) - in this case i get:

Run-time Error '5':
Invalid procedure call or argument

p.s. great forum
 
Upvote 0
"doesn't work" is a relative term. It might be due to your computer's resources or network capacity. I just stuck 400 sheets into a workbook, closed it, coded it as the workbook of interest, and the cbo populated just fine with all sheet names, no problem. At any rate, good luck with your project.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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