暂停 VBA 直到 #GETTING DATA Power Pivot 完成。

Question

我有一个具有大型数据模型的Excel工作簿，连接着两个切片器。我需要循环遍历每个切片器中的每个条目，允许工作簿在加载大量存储过程公式时进行等待，并将一个特定的工作表复制到另一个工作簿中。

我编写了VBA来完成所有这些操作，但我无法让VBA在工作簿完成上传之前等待，然后再继续执行脚本的其他部分。 可以排除基于后台刷新的解决方案，因为它们不适用于OLAP。 我在网上找到了各种解决方案，其中包括等待计算完成的建议，但是脚本会跳过这些行。

唯一看起来适用的解决方案涉及到识别由于选择器更改而更新的每个单元格，并循环遍历它们，直到它们不再显示 #GETTING DATA。

对于我的工作簿，这将是数百个要识别和检查的单元格，感觉不可持续。

"Applcation.Wait" 在工作簿暂停获取数据的时间内等待所选的时间量。

设置与数据模型连接的切片器的不同值并自动化一些输出感觉应该是常见任务，我们应该有解决方案。 运行Office 365。

Sub generate_all_forecasts()
'Cycle through all products and push forecast values to fcst_output'
Application.ScreenUpdating = True

Dim SC_products As SlicerCache
Dim selection, product_array As Variant
Dim push As Boolean

Set SC_products = ThisWorkbook.SlicerCaches("Slicer_PRODUCT_GROUPING_WRITTEN") 'The product slicer on the Inputs worksheet'

product_array = Range("product_array") 'Named range product_array on Tbl_Codes worksheet'

For Each p In product_array 'For each product'

push = WorksheetFunction.Index(Range("fcst_push_array"), WorksheetFunction.Match(p, product_array, 0)) 'Check if the product has been selected for this run'

If push = True Then
    
    If p = "Major Medical Plan" Then  'If "Major Medical" '
    selection = Array("[Query1 1].[PRODUCT_GROUPING_WRITTEN].&[Major Medical Plan - CMM]", _
    "[Query1 1].[PRODUCT_GROUPING_WRITTEN].&[Major Medical Plan - GMM]")  'selection will be both CMM and GMM'
    Else
    selection = Array("[Query1 1].[PRODUCT_GROUPING_WRITTEN].&[" & p & "]")  'Otherwse selection is the single product'
    End If

    SC_products.VisibleSlicerItemsList = selection  'Change slicer to current selection'

    'This is where the script needs to pause until #GETTING DATA is complete'

    Application.Run "push_to_output"  'Run the forecast update macro'
    
End If

Next p

Worksheets("Fcst_Output").Range("B2:B1381").Value = "" 'Clear prior month's comments'

Application.ScreenUpdating = True
End Sub

无效的解决方案：

• 更改 Power Pivot 切片器后等待时间
• 使 VBA 在继续之前等待
• 等待 Application.Calculate 完成

我真的不想用的解决方案： 强制 VBA 等待 Power Pivot 完成刷新

- OldKingCole

---

你应该能够使用以下QA添加延迟（尽管没有检查来测试枢轴是否已完成）：https://stackoverflow.com/q/49389093/3688861 - Tragamor

我会在工作日结束时尝试一下，因为为每个周期设置最小延迟会使其变成一个冗长的宏。宏是否有办法测量Power Pivot是否仍在获取数据？ - OldKingCole

可以将PowerQuery的后台刷新设置为false，但不确定PowerPivot是否也适用。另一种选择是将数据获取和操作部分移至Power Query，并设置标志(QueryTable.Refresh BackgroundQuery:=False)，然后在PowerQuery的最终输出上放置一个普通的数据透视表。 - usmanhaq

我不知道你的PowerPivot设置是如何的，也不知道挥发函数是否在更长时间更新之前计算切片器(https://support.microsoft.com/en-us/office/recalculate-formulas-in-power-pivot-1169df58-70f4-467f-aaa6-5eb25860598e)，但如果您可以在其中放置Today()或Now()，这通常不会自动更新(根据文档)，然后通过while循环检查直到它被更新，usmanhaq的答案可能更容易实现。 - Notus_Panda

很不幸，将背景刷新设置为 False 不会影响立方体函数，在这个模型中，立方体函数是最大的价值来源。更改切片机选择并不是查询刷新，添加传统数据透视表也不是一个选项。这个模型的创建是为了减少数据透视表的使用。 - OldKingCole

Answer 1

感谢Tragamor提供了一个已经有有效答案的帖子链接。我将以下内容立即包含在我的VBA代码中，并且它似乎正确地等待所有数据获取完成后再执行：

感谢Tragamor提供一个已经有可用答案的线程链接。我在我的 VBA 中在切片器选择之后立即加入以下内容，看起来可以正确等待所有数据获取完成：

    Dim CalculationState As Long
    With Application
        CalculationState = .Calculation
        .Calculation = xlCalculationAutomatic
        .CalculateUntilAsyncQueriesDone
        Do Until .CalculationState = xlDone
            DoEvents
        Loop
        .Calculation = CalculationState
    End With

- OldKingCole

Answer 2

找到你的查询属性，并将“启用后台刷新”设置为False（Microsoft默认使用True）。

然后在你的代码中，你需要调用RefreshAll并使用DoEvents等待数据加载。如果你想让数据更新在打开时运行，你可以在“ThisWorkbook”对象中使用它。

Private Sub Workbook_Open()
For Each q In ThisWorkbook.Connections
q.Refresh
DoEvents
Next
End Sub    

- Svein Arne Hylland

---

感谢您的输入。我需要的延迟与查询刷新无关，而是与当切片器更改时的重新计算有关。即使将“启用后台刷新”设置为false，VBA仍会在PowerPivot尝试获取数据和完成计算时快速跳过该切片选择。 - OldKingCole

感谢您提出这个问题。 当我从平面文件共享转向使用Power BI立方体时，我也遇到了同样的问题。 感谢您分享一个有效的答案。 - Svein Arne Hylland