Get worksheet names from closed workbook

If the workbook does not need to stay closed, this would work (modify for path, workbook name, and ThisWorkbook sheet name):

Sub Test1()
Workbooks.Open FileName:="C:\Your\File\Path\Book1.xls"
Dim ws As Worksheet
Dim i As Integer
With ThisWorkbook.Worksheets("Sheet1")
.Range("A:A").ClearContents
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
.Range("A" & i) = ws.Name
Next ws
End With
Workbooks("Book1").Close False
End Sub

Also a trick from Harlan Grove, enter the formula
'='C:\Your\File\Path\[Book1.xls]_'!A1
to see a placeholder list of sheet names

Thanks Tom...unfortunately, I can't open the workbook, so I need to extract the data from all the worksheets of this workbook to a new workbook. I have VB code to do this, but it requires that I know the worksheet names, hence, I need to retrieve all worksheet names from the book that I can't open. Any suggestions?

The following is a modification from the following post:

http://makeashorterlink.com/?U3C856C54

It returns a collection, not an array, due to the option of "repeated" names when there is a print area setup.

I tried using the GET.WORKBOOK(1, ...) XLM macro as well, but a) doesn't work well in VBA, and b) doesn't work with closed workbooks.

<font face=Courier New><SPAN style="color:darkblue">Option</SPAN> <SPAN style="color:darkblue">Explicit</SPAN>

<SPAN style="color:darkblue">Function</SPAN> GetSheetsNames(WBName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>) <SPAN style="color:darkblue">As</SPAN> Collection
    <SPAN style="color:green">'Needs a reference to:</SPAN>
    <SPAN style="color:green">'Microsoft ActiveX Data Object X.X Library</SPAN>
    <SPAN style="color:green">'Microsoft ADO Ext. X.X for DLL and Security</SPAN>
    
    <SPAN style="color:darkblue">Dim</SPAN> objConn <SPAN style="color:darkblue">As</SPAN> ADODB.Connection
    <SPAN style="color:darkblue">Dim</SPAN> objCat <SPAN style="color:darkblue">As</SPAN> ADOX.Catalog
    <SPAN style="color:darkblue">Dim</SPAN> tbl <SPAN style="color:darkblue">As</SPAN> ADOX.Table
    <SPAN style="color:darkblue">Dim</SPAN> sConnString <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
    <SPAN style="color:darkblue">Dim</SPAN> sSheet <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
    <SPAN style="color:darkblue">Dim</SPAN> Col <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">New</SPAN> Collection
    
    sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & WBName & ";" & _
                  "Extended Properties=Excel 8.0;"

    <SPAN style="color:darkblue">Set</SPAN> objConn = <SPAN style="color:darkblue">New</SPAN> ADODB.Connection
    objConn.<SPAN style="color:darkblue">Open</SPAN> sConnString
    <SPAN style="color:darkblue">Set</SPAN> objCat = <SPAN style="color:darkblue">New</SPAN> ADOX.Catalog
    <SPAN style="color:darkblue">Set</SPAN> objCat.ActiveConnection = objConn

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

<SPAN style="color:darkblue">Sub</SPAN> Test()
    <SPAN style="color:darkblue">Dim</SPAN> Col <SPAN style="color:darkblue">As</SPAN> Collection, Book <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
    Book = "C:\Your path\Your file.xls"
    <SPAN style="color:darkblue">Set</SPAN> Col = GetSheetsNames(Book)
    <SPAN style="color:darkblue">For</SPAN> i = 1 <SPAN style="color:darkblue">To</SPAN> Col.Count
        MsgBox Col(i)
    <SPAN style="color:darkblue">Next</SPAN> i
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Thanks Juan...that did it! Appreciate the help.

Aloha,
Rey

Hi All...
10 years latter! Here is another solution, much more simple, for closed Workbooks.

Function GetSheetsNames(file)
Set sh = GetObject(file).Worksheets
For Each c In sh
Debug.Print c.Name
Next
End Function

Sub Teste()
file = "C:\Your path\Your file.xls"
GetSheetsNames (file)
End Sub

But that opens the closed workbook which was what the OP to this old thread wanted to avoid.

I am using Excel 2010. When I execute the code, the compiler selects the line 'Dim objConn As ADODB.Connection' and provides a 'User Defined Type Not Defined' error. Can anyone help?

go into the VBE and on the menu bar Tools>References and set a reference to Microsoft ActiveX Data Objects Library (the latest version you have). The code should then work.

I hope this can be considered as "building on the thread" rather than hijacking it. If I'm out of order, let me know and I'll start a new thread. TIA.


I'm trying to use this code with an .xlsm file in Excel 2007.

However, I get a "Run-time error '-2147467259 (80004005)': External table is not in the expected format."

If I try changing "Extended Properties=Excel 8.0;" to "Extended Properties=Excel 12.0;", I get a "Run-time error '-2147467259 (80004005)': Could not find installable ISAM."

I have references to
Microsoft ActiveX Data Objects 6.1 Library and
Microsoft ADO Ext. 6.0 for DDL and Security

If I save the .xlsm file as an .xls file, the code will work. I'm hoping this is something simple as, otherwise, it's just what I'm looking for.

Regards, TMS