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.