Get Sheet number using ADOX


New Member
Jul 29, 2009
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
Set objCat = Nothing
Set objConn = Nothing
End Function

TIA... :)

Andrew Poulsom

MrExcel MVP
Jul 21, 2002
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
End Sub


New Member
Jul 29, 2009
Thanks a lot. Its working perfectly.
Hope i too can help some people here just as you were helping me a lot...

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...