返回带有excel宏的所有访问（.mdb）表的列表

I have an mdb file location defined in Sheet1 range B1. This value is:

我有一个在Sheet1范围B1中定义的mdb文件位置。这个值是：

"C:\Users\User\Desktop\Test.mdb"

“C：\用户\用户\桌面\ Test.mdb的”

What I am looking to do is generate a list of all the tables in this file and return it within excel. I have a partially working script but it is returning unwanted items

我要做的是生成此文件中所有表的列表，并在excel中返回它。我有一个部分工作的脚本，但它返回不需要的项目

I am working off of this:

我正在解决这个问题：

Sub GetTableNames()

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim lRow As Long
Dim szConnect As String

LastRowSetup = Worksheets("Setup").Cells(Rows.Count, 1).End(xlUp).Row 'last row where table names populate

If LastRowSetup < 10 Then
    LastRowSetup = 10 'so we dont accidentally clear important data above this
End If

Sheets("Setup").Range("A10:A" & LastRowSetup & "").ClearContents 'clear old data

fStr = Sheets("Setup").Range("C2").Value 'file location of mdb

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fStr & ";"

Set cnn = New ADODB.Connection
cnn.Open szConnect
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn

lRow = 10
For Each tbl In cat.Tables
Sheet1.Cells(lRow, 1).Value = tbl.Name
lRow = lRow + 1
Next tbl

cnn.Close
Set cat = Nothing
Set cnn = Nothing

End Sub

however it is returning many additional things that are NOT table names. for instance

但它返回了许多非表名的附加内容。例如

~TMPCLP313341
~TMPCLP74661
Approved_table1
Approved_table2    
MSysAccessStorage
MSysAccessXML
MSysACEs
MSysNameMap
MSysNavPaneGroupCategories
MSysNavPaneGroups
MSysNavPaneGroupToObjects
MSysNavPaneObjectIDs
MSysObjects
MSysQueries
MSysRelationships

when i physically open the mdb all i see are 'table 1' and 'table 2'. is there a way to implement an additional filter in the ADODB connection to not return all of the temp files and Msys objects or is this something i will just have to filter after importing.

当我实际打开mdb时，我看到的是'表1'和'表2'。有没有办法在ADODB连接中实现一个额外的过滤器，不返回所有临时文件和Msys对象，或者这是我在导入后必须过滤的东西。

note that i had to set a reference to the Microsoft ADO Ext. 2.X for DDL and Security object library as well as the normal ADO object library.

请注意，我必须设置对Microsoft ADO Ext的引用。 2.X表示DDL和Security对象库以及普通的ADO对象库。

2 个解决方案

#1

try this code:

试试这段代码：

For Each tbl In cat.Tables
 If Left(tbl.Name, 4) <> "MSys" And Left(tbl.Name, 1) <> "~" then
  Sheet1.Cells(lRow, 1).Value = tbl.Name
  lRow = lRow + 1
 end if
Next tbl

#2

is there a way to implement an additional filter in the ADODB connection to not return all of the temp files and Msys objects or is this something i will just have to filter after importing.

有没有办法在ADODB连接中实现一个额外的过滤器，不返回所有临时文件和Msys对象，或者这是我在导入后必须过滤的东西。

No, you will have to loop through the table names and simply ignore the system ("MSys...") and temporary ("~...") tables.

不，您将不得不遍历表名并简单地忽略系统（“MSys ...”）和临时（“〜...”）表。

#1