Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading sheet names in closed workbooks - Is it possible?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading sheet names in closed workbooks - Is it possible?
Tom Ogilvy wrote: Jake Marx has been a good source on using ADOX to do this: http://tinyurl.com/yuvqox -- Regards, Tom Ogilvy Thanks Tom. Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading sheet names in closed workbooks - Is it possible?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading sheet names in closed workbooks - Is it possible?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading values from a closed workbook | New Users to Excel | |||
Reading from a closed workbook | Excel Programming | |||
Return Sheet Names from Closed Workbook | Excel Programming | |||
Reading from a closed file. | Excel Worksheet Functions | |||
Reading Sheet Names/Index from closed file using Biff8. | Excel Programming |