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