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:
I can't seem to find any property to tell me the order...
Help?
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
[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?
Last edited: