Getting spreadsheet names from closed file using ADODB with excel 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

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Set cat = CreateObject("ADOX.Catalog")
Set cat.ActiveConnection = objConnection
Debug.Print cat.Tables.Count

For Each tbl Incat.Tables
Debug.Print tbl.Name
Debug.Print tbl.datecreated
Debug.Print tbl.datemodified
Next tbl

</code>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]

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">This is what I built the other day with DAO but I still want to figure out ADO...

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">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</code>
</code>Then to run I have a bunch of code for an open dialog, then it determines the format and the strings:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"><code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">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</code>
</code>Then I have:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">'Get Spreadsheets
Dim FileSpreadsheets() As String
FileSpreadsheets = GetSheets(FileToOpen, FileExt)</code></pre>Then you can do whatever you need to do but as an example to get a msgbox:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"><code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">mymsg = "Count: " & UBound(FileSpreadsheets) & vbNewLine & vbNewLine & _
"Sheets:" & vbNewLine & vbNewLine

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

MsgBox mymsg</code><code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">
</code>

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

 

[Fazza]

hello, Ahmed

I don't know about determining worksheet order without opening the file.

However, I'm wondering why you want that information: as perhaps some other approach can do what you want without the order being known?

What do you think? Regards, Fazza

 

[a_ahmed]

I got what I wanted now with DAO... as with ADO it does not give sheet order (another forum someone also found out for me that spaces are displayed first with ADO).

The reason for this? It's much faster than opening excel files and it allows for instant access to lots of files vs opening each file which is rather slow. It also allows me to treat the data once loaded as a SQL database so I can use SQL queries, but at the same time I can use excel functions.

 

[Jerry Sullivan]

Hi Ahmed,

Building on Fazza's question, if your objective is to make formulas and SQL queries, with few exceptions (like 3D formulas) those actions can be done by referencing the sheet names without knowing the sheet order.

What is it that you are trying to do that is dependent on the sheet order?

 

[a_ahmed]

The files I'm working with, under some conditions I don't know if there is more than one sheet and I may not know the exact name. The clients I work with may change spreadsheet names, add spreadsheets etc... so it doesn't quite work. I can't and don't want to guess and need to know the order of things.