Excel VBA - 刷新选定的查询/连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27351137/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Refresh selected queries / connections
提问by tospig
Situation
情况
I have 5 Workbook connectionsset up that query data from an MS Access database, and I have given them the names qry_1, qry_2, ... , qry_5
我Workbook connections从 MS Access 数据库中设置了5 个查询数据，并为它们指定了名称qry_1, qry_2, ... ,qry_5
I have a selection of these query names listed in a table called qry_Tableon a worksheet:
我qry_Table在工作表上调用的表中列出了这些查询名称的选择：
Query Name
----------
qry_1
qry_4
qry_5
and I can loop through the table (using .listObjects) to get each query name as a string
我可以遍历表（使用.listObjects）来获取每个查询名称作为string
Question
题
I can loop through all the queries in the workbook and refresh them all, but I can't seem to work out how to select and refresh only those queries listed in the table, namely qry_1, qry_4and qry_5.
我可以遍历工作簿中的所有查询并将它们全部刷新，但我似乎无法弄清楚如何仅选择和刷新表中列出的那些查询，即qry_1,qry_4和qry_5。
I would like to be able to setthe query object so that I can define the .Connectionstring too.
我希望能够set查询对象，以便我也可以定义.Connection字符串。
Is this possible?
这可能吗？
Notes
笔记
The code here shows how I get each row in the column Query Nameof the table qry_Table
这里的代码显示了我如何获取Query Name表格列中的每一行qry_Table
dim wksControl As worksheet
dim objList As ListObject
set wksControl = worksheets("Control") 'the worksheet that contains 'qry_Table'
Set objList = wksControl.ListObjects("qry_Table")
With objList.ListColumns("Query Name").DataBodyRange
For i = 1 To .Rows.count
str = .Rows(i) 'query name to refresh
'***Required: Define the qry to refresh
Set qry = .QueryTable(str) '<~~ this code fails
'code to .Refresh BackgroundQuery:=False
Next i
End With
And to refresh all the queries in the workbook I can use
并刷新我可以使用的工作簿中的所有查询
'code for generic query connections
For Each objList In wks.ListObjects
If objList.SourceType = xlSrcQuery Then 'only refresh if it's the right query type
'ensure it's using the right connection/database as specified on the Control sheet
strConnection = "ODBC;DSN=MS Access Database;DBQ=" & Range("dbFilePath") & _
Range("dbName") & ";DefaultDir=" & Range("dbFilePath") & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
With objList.QueryTable
.Connection = strConnection
.BackgroundQuery = False
.Refresh
End With
count = count + 1
End If
Next
回答by Rory
You need to refer to the ListObjectby name, rather than the connection, and then access its Querytableproperty:
您需要引用ListObjectby 名称，而不是连接，然后访问其Querytable属性：
With wks.Listobjects(str).QueryTable
回答by Raugmor
by using str = .Rows(i)you are assigning whole row's range to str, replacing the line with str = .Cells(i,1).Valueshould work as it will return value in first column of the irow.
通过使用str = .Rows(i)您将整行的范围分配给 str，替换该行str = .Cells(i,1).Value应该可以工作，因为它将在该i行的第一列中返回值。
