使用ADODB从关闭的文件中以Tab键顺序返回工作表名称

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

我能够连接和查询特定的工作表。某些文件可能具有不同或更改的第一个工作表名称，有时可能有多个文件。

I tried a few different functions to return a full list. However none give me the worksheet tab names in the order they appear in Excel.

我尝试了一些不同的函数来返回完整的列表。但是，没有一个按照它们在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 I could determine by datecreated or datemodified, but the dates on all 4 are the same.

我以为我可以通过datecreated或datemodified来确定，但所有4的日期都是相同的。

> 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

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

因此，它为我提供了工作表选项卡名称列表的字母排序。

However the worksheet order is:

但是，工作表顺序是：

>[Sheet1][Avion][Sheet2][Meow]

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

我找不到任何财产告诉我订单。

2 个解决方案

#1

Adapted from: https://www.mrexcel.com/forum/excel-questions/406243-get-sheet-number-using-adox.html

改编自：https：//www.mrexcel.com/forum/excel-questions/406243-get-sheet-number-using-adox.html

Sub GetSheetNames()
    '###Requires a reference to Microsoft DAO x.x Object Library

     Dim FName As String, i As Long, WB As DAO.Database

     FName = ThisWorkbook.Path & "\ADOXSource.xlsx"


     Set WB = OpenDatabase(FName, False, True, "Excel 8.0;")

     With WB.tabledefs
         For i = 1 To .Count
             Debug.Print i, .Item(i - 1).Name '<< TableDefs Is zero based
         Next i
     End With

     WB.Close

End Sub

#2

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

这是我前几天用DAO建造的，但我仍然想弄清楚ADO ......

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:

然后运行我有一堆打开对话框的代码，然后它确定格式和字符串：

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:

然后我有：

'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:

然后你可以做任何你需要做的事情，但作为一个例子来获得一个msgbox：

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

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

MsgBox mymsg

#1