I need help getting spreadsheet names from closed file using ADODB with tab order

[a_ahmed]

I am able to successfully connect and query specific sheets. Some files I am dealing with may have the first spreadsheet name different or changing and sometimes there may be more than one.

i tried to write a few different functions to return a full list. However none give me the spreadsheet tab names in the order they appear in excel...

The easiest method is this

Set cat = CreateObject("ADOX.Catalog")
Set cat.ActiveConnection = objConnection
Debug.Print cat.Tables.Count

For Each tbl In cat.Tables
Debug.Print tbl.Name
Debug.Print tbl.datecreated
Debug.Print tbl.datemodified
Next tbl

I thought maybe i could determine by datecreated or datemodified, but the dates on all 4 are the same

This prints for me:
Avion$
3/17/2017 12:43:19 PM
3/17/2017 12:43:19 PM
Meow$
3/17/2017 12:43:19 PM
3/17/2017 12:43:19 PM
Sheet1$
3/17/2017 12:43:19 PM
3/17/2017 12:43:19 PM
Sheet2$
3/17/2017 12:43:19 PM
3/17/2017 12:43:19 PM

However the actual spreadsheet order is:
<Sheet1><Avion><Sheet2><Meow>

So it gives me alphabetic sorting of the spreadsheet tab name list...

I can't seem to find any property to tell me the order...

Help?

[a_ahmed]

This is what I built the other day with DAO but I still want to figure out ADO...

Code: Select all

Public Function GetSheets(ByVal FileToOpen As String, ByVal FileExt As String)
    Dim Shts() As String, ShtCnt As Integer: ShtCnt = 0
    ReDim Shts(0 To ShtCnt)

    Dim dbE As Object, db As Object, tbl As Object

    On Error Resume Next
    Set dbE = CreateObject("DAO.DBEngine")
    Set dbE = CreateObject("DAO.DBEngine.35")
    Set dbE = CreateObject("DAO.DBEngine.36")
    On Error GoTo 0

    Set db = dbE.OpenDatabase(FileToOpen, False, False, FileExt & ";HDR=Yes;")

    For Each tbl In db.TableDefs
       Shts(ShtCnt) = Mid(tbl.Name, 1, Len(tbl.Name) - 1)
       ShtCnt = ShtCnt + 1
       ReDim Preserve Shts(0 To ShtCnt)
    Next

    Set dbE = Nothing
    Set db = Nothing
    Set tbl = Nothing

    GetSheets = Shts
End Function

Then to run I have a bunch of code for an open dialog, then it determines the format and the strings:

Code: Select all

Select Case Right(FileToOpen, Len(FileToOpen) - InStrRev(FileToOpen, "."))
    Case "xls", "XLS"
        Provider = "Microsoft.Jet.OLEDB.4.0;"
        FileExt = "Excel 8.0"
    Case "xlsx", "XLSX"
        Provider = "Microsoft.ACE.OLEDB.12.0;"
        FileExt = "Excel 12.0"
    Case "csv", "CSV"
        Provider = "Microsoft.Jet.OLEDB.4.0;"
        FileExt = "Excel 8.0"
    Case Else
        GoTo Err:
End Select

Then I have:

Code: Select all

'Get Spreadsheets
Dim FileSpreadsheets() As String   
FileSpreadsheets = GetSheets(FileToOpen, FileExt)

Then you can do whatever you need to do but as an example to get a msgbox:

Code: Select all

mymsg = "Count: " & UBound(FileSpreadsheets) & vbNewLine & vbNewLine & _
"Sheets:" & vbNewLine & vbNewLine

For Each Sheet In FileSpreadsheets
    mymsg = mymsg + Sheet & vbNewLine
Next Sheet

MsgBox mymsg

However I'd like to figure this out with ADO... since DAO is defunct by