Retrieve all sheet names from a closed workbook

[nk18]

Hello,

Could anyone give me any pointers of how to retreive all sheet names from a closed workbook and then populate a listbox with them? I know you can use ExecuteExcel4Macro but I don't really understand how to use it. Thanks for any help.

nk18

 

[Joe Was]

Alt+F11 then VBE Toolbar: Insert - Module
Paste the code below in Editor's code window.
Click the top-most right Close "X" to return to your Sheet.

Excel ToolBar: Tools - Macro - Macros..., Select Macro's Name, Click "Run"


Sub myWBSheets()
'Standard module code, like: Module1.
Dim strMySheets$, strMyWBName$
Dim strMyFileTypeExt$, strMyFileTypesToUse$, strMyFileToOpen$

'*********** Change File Type To Use Here! ***********************************
'Set "strMyFileTypeExt" to your Custom File Extention Type, here only!
strMyFileTypeExt = "xls"
'************************************************************************************
'ChDir "C:\Whatever\Whatever" '<== Optional new Folder here!

On Error GoTo myEnd
strMyFileTypesToUse = "Data Files (*." & strMyFileTypeExt & "), *." & strMyFileTypeExt

strMyFileToOpen = Application.GetOpenFilename(Title:="Select a file to work with!", _
FileFilter:="(" & strMyFileTypesToUse & """)", MultiSelect:=False)

Application.ScreenUpdating = False
Workbooks.Open (strMyFileToOpen)

For Each Sheet In ActiveWorkbook.Sheets
strMySheets = strMySheets & vbLf & Sheet.Name
strMyWBName = ActiveWorkbook.Name
Next Sheet

Application.ScreenUpdating = True
Application.DisplayAlerts = False

ActiveWindow.Close
Application.DisplayAlerts = True

MsgBox "Workbook:" & Space(3) & """" & strMyWBName & """" & vbLf & _
"Contains these Sheets:" & vbLf & _
strMySheets, _
vbInformation + vbOKOnly, _
"List Sheets!"

myEnd:
End Sub

 

[nk18]

Hi Joe Was,

Apologies for the delayed response. That's great I've adapted that to go in the workbook open event and populate a worksheet instead of a msgbox.

Thanks again,

nk18

 

[Richard Schollar]

Hi Joe

I'd be interested to know if there was a way to retrieve the sheet names from an actual closed workbook (ie one that wasn't opened by the procedure) - presumably it must be possible?

 

[Joe Was]

I know you can pull data from a closed WB, like a 3D reference with workbook name and sheet qualified.
You can also check for a file without opening it:

For x = 1 To myBottom 'Loop to validate filenames
myTest = ActiveCell
Filename = myFolder & "\" & ActiveCell

If myTest = "" Then End 'Blank line terminates

'Test for file!
Set fs = Application.FileSearch
With fs
.LookIn = myFolder
.Filename = myTest

'MsgBox CurDir
'MsgBox "Now looking for: " & Filename

If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
'Found file!
ActiveCell.Offset(0, 1).Select
'Mark file as found!
ActiveCell.Value = "Found"
ActiveCell.Offset(0, -1).Select
Else

'File not found!
'MsgBox "CurDir: " & CurDir

Response = MsgBox("Folder: " & myFolder & vbCr & _
vbCr & "File ==> " & myTest & ", was not Found?" & vbCr & vbCr & _
Filename & ", is Missing?" & vbCr & vbCr & _
vbCr & "Note: Hit ""Ctrl - Break"" to abort this search!", _
vbOKOnly, "Missing File!")
'Missing file!

ActiveCell.Offset(0, 1).Select
'Mark file as Missing!
ActiveCell.Value = "Missing"
ActiveCell.Offset(0, -1).Select
End If

End With

ActiveCell.Offset(1, 0).Select
Next x


Or


Private Function FileExists(fTag, fName, fExt) As Boolean
'Custom Sheet Function!
'Standard module code, like: Module1.
'Syntax: =FileExists(,FileName or cell Location,)
'If file is found: Returns TRUE, else FALSE.
Dim myFolder As String

fTag = "C:\"
fExt = ".txt"
myFolder = Dir(fTag & fName & fExt)
'MsgBox fName

'To activate help input: FileExists(,"Help",)
If UCase(fName) = "HELP" Then
MsgBox " Syntax: =FileExists("",FileName,"")" & vbCr & vbCr & _
"Like ==> =FileExists("",Test,"")"
End If

If myFolder <> "" Then
FileExists = True
Else

FileExists = False
End If
End Function


Like you I think it can be done, yet I have never seen it done nor have I found a direct way of doing it?

 

[DominicB]

Hi all

This code seems to work OK. I believe the originator of this code is Jamie Collins.

Sub GetSheetNames()
Dim objConn As Object
Dim objCat As Object
Dim tbl As Object
Dim iRow As Long
Dim sWorkbook As String
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

sWorkbook = "c:\test.xls"
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sWorkbook & ";" & _
"Extended Properties=Excel 8.0;"
Set objConn = CreateObject("ADODB.Connection")
objConn.Open sConnString
Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConn
iRow = 1
For Each tbl In objCat.Tables
sTableName = tbl.Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
'Worksheet name with embedded spaces enclosed by single quotes
If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
iTestPos = 1
iStartpos = 2
End If
'Worksheet names always end in the "$" character
If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
Cells(iRow, 1) = Mid$(sTableName, iStartpos, cLength - _
(iStartpos + iTestPos))
iRow = iRow + 1
End If
Next tbl
objConn.Close
Set objCat = Nothing
Set objConn = Nothing
End Sub

DominicB

 

[Richard Schollar]

Excellent Dominic - just what I was looking for! I figured something like that must be possible because when using eg MSQuery on a closed workbook the System Tables contain the sheet names. Thanks again

 

[erik.van.geit]

Hi,

see for very similar code
http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/

ps. i think it was posted by Jake Marx(?) 1st

have a nice weekend
Erik