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

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

HTH
--
AP

"Marc Gendron" <Marc a écrit dans le
message de news: ...
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

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

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" wrote:

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

Hi Marc

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

Have a nice day

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



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

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

Marc

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

His day job is Chrysanthemum growing.


Gord

On Tue, 4 Jul 2006 13:56:01 -0700, Marc Gendron
wrote:

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" wrote:

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