在excel中，有没有办法对"Refresh All"按钮进行编程，以便它一次批量加载查询？

Question

我在Microsoft Excel文件中有~150个查询。点击"刷新全部"会冻结我的电脑，导致一些数据无法正确加载，即使网络连接良好。

我正在寻找一种方法来编程"刷新全部"按钮，这样它可以一次加载5到10个查询，然后移动到下一个。我手动尝试过，它加载没有任何问题。一次150个查询太多了。

谢谢。

Answer 1

我找不到任何简单的方法来解决您的查询，但我有一种变通方法的一些想法。下面您可以找到两个可能对您有所帮助的VBA宏。第一个代码在新选项卡中列出工作簿中的所有查询:

Sub ListQueries()
'Add tab to list all queries
Dim wsQueries As Worksheet
Set wsQueries = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
wsQueries.Name = "Query list"
wsQueries.Range("a1").Value = "Query name"
Dim con As WorkbookConnection
For Each con In ThisWorkbook.Connections
If UCase(Left(con.Name, 8)) = "QUERY - " Then wsQueries.Range("a1048576").End(xlUp).Offset(1, 0).Value = con.Name
Next con
End Sub

当它完成时，你可以使用第二个。这一次，它将循环遍历所有查询并刷新它们，但只有您在此子句If counter = 10 Then Exit For中定义的数量-如果您想要15，则可以随意修改它。对于每个引用的查询，它将在b列中添加'Yes'。当你运行RefreshQueries时，它首先检查查询是否标记为'Yes'，如果为真则忽略它。

Sub RefreshQueries()
Dim counter As Byte
counter = 0
'Range with query names
Dim rQueries As Range
Dim rQuery As Range
Dim wsQueries As Worksheet
Set wsQueries = ThisWorkbook.Worksheets("Query list")
Set rQueries = wsQueries.Range("a2:a" & wsQueries.Range("a1048576").End(xlUp).Row)
wsQueries.Range("b1") = "Refreshed"
For Each rQuery In rQueries
If counter = 10 Then Exit For 'if more than 10 queries refreshed then exit loop
'If query is refreshed then omit it and go to next
If rQuery.Offset(0, 1) <> "Yes" Then
ThisWorkbook.Connections(rQuery.Value).Refresh
rQuery.Offset(0, 1) = "Yes"
counter = counter + 1
End If
Next rQuery
End Sub

总而言之，你应该运行ListQueries一次，并运行RefreshQueries尽可能多的次数来刷新它们。