Is the list of Worksheets limited to "This Workbook"

[Guest]

Hi Excel wizs,

Is it possible to list the worksheets of a closed workbook? More !!! Is it
possible to list the worksheets of a number of closed workbooks ? ( the ones
in "C:\MyDocuments", for example)

Ideally, I would like to do it at runtime, but I could settle for a list
being updated by each new workbook created.

I hope I've been clear enough. If any of you's can put me on the right
track, I'd appreciate it.

Thanks,
Marc

 

[Ardus Petus]

You have to (temporarily) open each workbook to access its worksheets.

HTH

 

[Ron de Bruin]

Hi Marc

Bob posted this macro that use ADO to copy the sheet names of a closed workbook in the activesheet.

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:\Data\test1.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

 

[Guest]

Thanks, you've been helpful.

(is this some kind of vocation for you or are you remunerated in some way?)

Thanks again
Marc

 

[Ron de Bruin]

Hi Marc

No it is a Hobby and it is fun to help people

Have a nice day

 

[Gord Dibben]

Marc

Ron does this just for the satisfaction he gains by helping others.

His day job is Chrysanthemum growing.


Gord