高手来指点，如何利用excel vba，将数据库中所有含有指定字段的表名，罗列在excel的A列

发布网友发布时间：2022-04-08 08:49

共1个回答

热心网友时间：2022-04-08 10:19

要通过EXCEL查询指定数据库中的含有指定字段的表名时，首先得知道查询SQL，查询SQL如下：

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='字段名'

如果要写成VBA的话，下面给你一段我写的宏，测试过了：

－－－－－－－－－－－

Sub 宏7()

' 宏7 宏

Application.CutCopyMode = False

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _

"ODBC;DRIVER=SQL Server;SERVER=服务器IP;UID=sa;;APP=Microsoft Office 2016;WSID=GUESS;DATABASE=要查询的数据库名" _

, Destination:=Range("$A$1")).QueryTable

.CommandType = xlCmdSql

.CommandText = Array( _

"SELECT TABLE_NAME" & Chr(13) & "" & Chr(10) & "FROM 要查询的数据库名.INFORMATION_SCHEMA.COLUMNS" & Chr(13) & "" & Chr(10) & "WHERE (COLUMN_NAME='要查询的字段名')")

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

Dim chars As String

Dim rndstr As String

chars = "ABCDEFGHJKLMNPQRSTUVWXYZ0123456789"

Randomize

For i = 1 To 6

rndstr = rndstr & Mid(chars, Int(Rnd() * Len(chars) + 1), 1)

.ListObject.DisplayName = rndstr

.Refresh BackgroundQuery:=False

End With

End Sub

最后结果如下图：