Get Sheet number using ADOX

[rahul_nair01]

Hi All,
I m using this function to get the Sheet names in a workbook. But any idea on how i can tweak it so that i do get only the second sheet name of the workbook instead of all. Not able to figure out how to get the specific sheet number.

Function GetSheetsNames(WBName As String) As Collection
'Needs a reference to:
'Microsoft ActiveX Data Object X.X Library
'Microsoft ADO Ext. X.X for DLL and Security
Dim objConn As New ADODB.Connection
Dim objCat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim sConnString As String
Dim sSheet As String
Dim col As New Collection
Dim col2 As New Collection
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WBName & ";" & _
"Extended Properties=Excel 8.0;"
Set objConn = New ADODB.Connection
objConn.Open sConnString
Set objCat = New ADOX.Catalog
Set objCat.ActiveConnection = objConn
MsgBox objCat.Tables.Item(4).Name + "::" + Str(objCat.Tables.Item(4).Columns(0).Attributes)
For Each tbl In objCat.Tables
sSheet = tbl.Name
sSheet = Application.Substitute(sSheet, "'", "")
sSheet = Left(sSheet, InStr(1, sSheet, "$", 1) - 1)
On Error Resume Next
col.Add sSheet, sSheet
On Error GoTo 0
Next tbl
Set GetSheetsNames = col
objConn.Close
Set objCat = Nothing
Set objConn = Nothing
End Function

TIA...

 

[Andrew Poulsom]

I don't think it's possible with ADOX. But you can use DAO:

Sub GetSecondSheetName()
'   Requires a reference to Microsoft DAO x.x Object Library
'   Adjust to suit
    Const FName As String = "P:\Temp\MrExcel\Temp\SheetNames.xls"
    Dim WB As DAO.Database
    Dim strSheetName As String
    Set WB = OpenDatabase(FName, False, True, "Excel 8.0;")
'   TableDefs is zero based
    strSheetName = WB.TableDefs(1).Name
    MsgBox strSheetName
    WB.Close
End Sub

 

[rahul_nair01]

Thanks a lot. Its working perfectly.
Hope i too can help some people here just as you were helping me a lot...