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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Marc Gendron" <Marc wrote in message ...
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