Reading sheet names in closed workbooks - Is it possible?

[Ken Johnson]

Say there are a number of workbooks in a folder. Each sheet's name in
each workbook is an employee's name.
Is it possible to have another workbook in that folder with code that
can compile a list of employee's names (sheet names) and workbook
names.

Or, do the workbooks all have to be opened before code can compile such
a list.

Any ideas?

Ken Johnson

 

[Ken Johnson]

Jake Marx has been a good source on using ADOX to do this:

http://tinyurl.com/yuvqox

Thanks Tom.

Ken Johnson

 

[Ken Johnson]

Hi Tom,

I tried this one...

Sub GetSheetNames(rsFilePath As String)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog

cat.ActiveConnection = "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=" & rsFilePath
For Each tbl In cat.Tables
If Right$(tbl.Name, 1) = "$" Then _
MsgBox Left$(tbl.Name, Len(tbl.Name) - 1)
Next tbl

Set cat = Nothing
End Sub

Nothing happened.

I discovered that the conditional test...

If Right$(tbl.Name, 1) = "$" Then

failed for every sheet because, for example, with a sheet named John
Smith...

Right$(tbl.Name, 1) = "'", not "$", because tbl.Name = "'John Smith$'",
not "John Smith$".

So, I've had to amend the code to accommodate the leading and trailing
apostrophes...

If Right(tbl.Name, 2) = "$'" Then _
MsgBox Mid(tbl.Name, 2, Len(tbl.Name) - 3)

Any idea as to why I would get these apostrophes that Jake's code
doesn't expect?

Also, Right and Right$ work the same don't they?

Ken Johnson

 

[Ken Johnson]

Thanks Tom and Martin,

It all makes sense now!
So I'll have to extend the code to cater for sheet names with and
without spaces.

Ken Johnson