如何让Excel VBA仅在查询数据连接的查询完成刷新之后才运行下一个代码

Question

我意识到，即使查询的Refresh首先完成，首先，刷新查询下面的代码甚至在完全提取数据库信息之前就运行。这将使我的分析差距。示例代码如下：

ActiveWorkbook.Connections("Query - Entries").Refresh
Total_rows_OCVoucher = Workbooks("Accounting Summary.xlsm").Worksheets("Voucher").Range("B" & Rows.Count).End(xlUp).Row
Total_rows_AccSummary = Workbooks("Accounting Summary.xlsm").Worksheets("Compiled Expenses").Range("A" & Rows.Count).End(xlUp).Row
x = 1
For j = 2 To Total_rows_OCVoucher
    Workbooks("Accounting Summary.xlsm").Worksheets("Compiled Expenses").Cells(Total_rows_AccSummary + x, 1) = Format(Workbooks("Accounting Summary.xlsm").Worksheets("Voucher").Cells(j, 2), "Short Date")
    Workbooks("Accounting Summary.xlsm").Worksheets("Compiled Expenses").Cells(Total_rows_AccSummary + x, 2) = Workbooks("Accounting Summary.xlsm").Worksheets("Voucher").Cells(j, 3)
    Workbooks("Accounting Summary.xlsm").Worksheets("Compiled Expenses").Cells(Total_rows_AccSummary + x, 3) = Workbooks("Accounting Summary.xlsm").Worksheets("Voucher").Cells(j, 9)
    Workbooks("Accounting Summary.xlsm").Worksheets("Compiled Expenses").Cells(Total_rows_AccSummary + x, 4) = "Outflow"
    Workbooks("Accounting Summary.xlsm").Worksheets("Compiled Expenses").Cells(Total_rows_AccSummary + x, 5) = Application.WorksheetFunction.Text(Workbooks("Accounting Summary.xlsm").Worksheets("Voucher").Cells(j, 2), "mmmm")
    Workbooks("Accounting Summary.xlsm").Worksheets("Compiled Expenses").Cells(Total_rows_AccSummary + x, 6) = Year(Workbooks("Accounting Summary.xlsm").Worksheets("Voucher").Cells(j, 2))
    Workbooks("Accounting Summary.xlsm").Worksheets("Compiled Expenses").Cells(Total_rows_AccSummary + x, 7) = Workbooks("Accounting Summary.xlsm").Worksheets("Voucher").Cells(j, 1)
    x = x + 1
Next j

如何让所有查询在转到下一行代码之前先完成计算？

Answer 1

替换：

ActiveWorkbook.Connections("Query - Entries").Refresh

with：

With ActiveWorkbook.Connections("Query - Entries")
    .OLEDBConnection.BackgroundQuery = False
    .Refresh
End With