使用 ADODB 从关闭的文件中按 Tab 键顺序返回工作表名称 [英] Return worksheet names in tab order from closed file using ADODB

问题描述

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

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.

我尝试了几个不同的函数来返回一个完整的列表.但是，没有人按照它们在 Excel 中出现的顺序给我工作表选项卡名称.

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.

最简单的方法是这个.

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

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

I thought 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

所以它给了我工作表标签名称列表的字母排序.

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

但是工作表顺序是:

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

我找不到任何属性来告诉我订单.

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

推荐答案

改编自: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

这篇关于使用 ADODB 从关闭的文件中按 Tab 键顺序返回工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！