vba Excel Querytable Refresh 仅工作一次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16460552/
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 Querytable Refresh only works once
提问by steveo40
I am adding a ListObject to an Excel 2007 Workbook using VBA. The ListObject is to have a QueryTable behind it, linking to an Access database. The code looks like this:
我正在使用 VBA 将 ListObject 添加到 Excel 2007 工作簿。ListObject 后面有一个 QueryTable，链接到 Access 数据库。代码如下所示：
Dim l As ListObject
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
c.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myAccessDatabasePath;Persist Security Info=False;"
Set r = New ADODB.Recordset
r.Open "Select * From myTable", c
Set l = ActiveSheet.ListObjects.Add(xlSrcQuery, r, True, xlYes, Range("A1"))
l.QueryTable.Refresh False
'this line causes an error
l.QueryTable.Refresh False
Essentially the problem is that I cannot refresh the table more than once. The Refresh button on both the Data and the Table Design ribbons is greyed out. I have tried similar code without using Listobjects (i.e. just QueryTables) and get the same problem. I have tried refreshing the underlying connection object and again, get the same problem.
本质上，问题是我不能多次刷新表格。数据和表格设计功能区上的刷新按钮均呈灰色。我在不使用 Listobjects（即只是 QueryTables）的情况下尝试了类似的代码并遇到了同样的问题。我尝试刷新底层连接对象，再次遇到同样的问题。
I've spent all morning Googling to no avail.
我整个上午都在谷歌上搜索无济于事。
Is this a bug, designed behaviour or (most likely) am I doing something stupid?
这是错误，设计行为还是（很可能）我在做一些愚蠢的事情？
Many thanks in advance,
提前谢谢了，
Steve
史蒂夫
采纳答案by steveo40
Ok, I got it to work. The macro recorder (thanks for the suggestion Dick) was actually useful for once.
好的，我让它工作了。宏记录器（感谢 Dick 的建议）实际上曾经有用过一次。
Dim s As Worksheet
Dim l As ListObject
Set s = ActiveSheet
Set l = s.ListObjects.Add(xlSrcExternal, "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myDatabasePath;", True, xlYes, Range("A1"))
l.QueryTable.CommandType = xlCmdTable
l.QueryTable.CommandText = "mytable"
l.QueryTable.Refresh False
'this now works!
l.QueryTable.Refresh False
回答by user2140261
This is UNTESTEDbut it still should work, it will check if the table is already in a refresh and if it is, it will wait 1 second and check again until it is no longer refreshing then it will continue
这是未经测试的，但它仍然应该工作，它会检查表是否已经在刷新，如果是，它会等待 1 秒并再次检查直到它不再刷新然后它会继续
Dim l As ListObject
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
c.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myAccessDatabasePath;Persist Security Info=False;"
Set r = New ADODB.Recordset
r.Open "Select * From myTable", c
Set l = ActiveSheet.ListObjects.Add(xlSrcQuery, r, True, xlYes, Range("A1"))
With l
.QueryTable.Refresh False
Do while .Refreshing
Application.Wait Now + TimeValue("00:00:01")
Loop
'this line causes an error
.QueryTable.Refresh False
End With
