New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

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

Macros, VBA, Excel Automation, etc.

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

Postby a_ahmed » Fri Mar 17, 2017 12:13 pm

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? :(
  • 0

a_ahmed
Rookie
 
Posts: 2
Joined: Oct 5, 2016
Reputation: 0
Excel Version: 2013

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

Postby a_ahmed » Sun Mar 19, 2017 5:28 am

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
  • 0

a_ahmed
Rookie
 
Posts: 2
Joined: Oct 5, 2016
Reputation: 0
Excel Version: 2013


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 10 guests