Accessing Closed Workbook Information

[Loomah]

Hi all
I posted a question yesterday regarding getting the name of the first
worksheet in a closed workbook. Bob Phillips posted some code which, while
it didn't do exactly what I was looking for, helped me immensely to move a
step closer to what I want. I've copied the code so far below.

The problem is that the tables (it appears to me) are not indexed in the
order the appear in the workbook but rather in the order they were created.
The other problem is I don't fully understand all the code so I may be
missing something!

Any further suggestions or is there a completely different approach?
Is there any way to count the number of sheets in a book in this way and use
the sheet indexes?

TIA

Function GetSheetName(fName As String) As String
Dim objConn As Object
Dim objCat As Object
Dim tbl As Object
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer


sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fName & ";" & _
"Extended Properties=Excel 8.0;"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open sConnString
Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConn

sTableName = objCat.tables(0).Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
'Worksheet name with embedded spaces are 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 it is always the case is the test necessary?
If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
GetSheetName = Mid$(sTableName, iStartpos, cLength -
(iStartpos + iTestPos))
End If

objConn.Close
Set objCat = Nothing
Set objConn = Nothing

End Function

 

[Bob Phillips]

Loomah,

It will be very difficult to get the order as they appear in the workbook,
as this can be totally arbitrary. Although a For Each loop on an open
workbook will cycle through them in the order that they appear in the
workbook, on a closed workbook it seems to go through them in index order. I
suppose this is because with the workbook being closed there is no
information held with the workbook about the presentation order, so it uses
the indexes as it is all the information it has to determine an order.

I can't think of any code that will achieve your objective, unless the user
stored some data in the workbook that gives those details, or you open the
workbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)