4

I have an Excel workbook with a large Data Model connected to two slicers. I need to cycle through every entry in the slicer, allow the workbook to catch up on loading a large number of cube formulas, then copy one particular worksheet over into another.

I've written VBA which does all of this, but I can't get the VBA to wait for the workbook to finish uploading before it continues with the rest of the script.
I can rule out background refresh-based solutions, which don't apply to OLAP.
Various solutions I've found online which recommend waiting for calculations to be complete don't to work. The script barrels through those lines.

The only solution I've seen which seems to apply involved identifying every cell which would be updated as a result of the slicer change and looping through them until they no longer say #GETTING DATA.

For my workbook, this would be hundreds of cells to identify and check and feels unsustainable.

Applcation.Wait waits for the selected amount of time during which the workbook pauses getting data.

Setting different values of a slicer connected to a Data Model and automating some output feels like it should be such a common task that we have a solution for.

Running 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

Solutions which have not worked:

The solution I really don't want to use: Force VBA to wait until power pivot finishes refreshing

double-beep
  • 5,031
  • 17
  • 33
  • 41
OldKingCole
  • 115
  • 12
  • 2
    You should be able to add a delay with the following QA (although there is no check to test the pivot has finished): https://stackoverflow.com/q/49389093/3688861 – Tragamor Nov 23 '22 at 18:03
  • I'll give that a shot at the end of the work day, as setting a minimum delay for every cycle will turn this into a lengthy macro. Is there really no way for a macro to measure whether or not power pivot is still getting data? – OldKingCole Nov 28 '22 at 14:31
  • 1
    Background refresh can be set to false for the PowerQuery, don't know about PowerPivot but an option can be to move your data fetching and manipulation part to Power Query and set the flag( QueryTable.Refresh BackgroundQuery:=False) and place a normal pivot on the final output of PowerQuery – usmanhaq Nov 29 '22 at 04:18
  • I don't know how your PowerPivot is set up nor if volatile functions are calculated before longer taking updates of slicers (https://support.microsoft.com/en-us/office/recalculate-formulas-in-power-pivot-1169df58-70f4-467f-aaa6-5eb25860598e) but if you could have a Today() or Now() in there which normally doesn't auto-update (according to the documentation) and then check in a while loop until it is updated? usmanhaq's answer will likely be easier to implement – Notus_Panda Nov 29 '22 at 10:45
  • Unfortunately, setting background refresh to False doesn't affect cube functions, which are the largest source of value in this model. Changing a slicer selection isn't a query refresh, and adding traditional pivot tables isn't an option. This model was created to reduce the use of pivot tables – OldKingCole Dec 12 '22 at 15:20

2 Answers2

1

Thanks to Tragamor for linking to a thread where they already had a working answer. I included the following immediately after the slicer selection in my VBA and it appears to properly wait until all data fetching is complete:

    Dim CalculationState As Long
    With Application
        CalculationState = .Calculation
        .Calculation = xlCalculationAutomatic
        .CalculateUntilAsyncQueriesDone
        Do Until .CalculationState = xlDone
            DoEvents
        Loop
        .Calculation = CalculationState
    End With
OldKingCole
  • 115
  • 12
0

Find your Query Properties, and set 'Enable background refresh' to False (Microsoft use True as default).

Then in your code you need to call for RefreshAll and wait for the data to load with the DoEvents. If you want your update of data to run on open you can use this in 'ThisWorkbook' Object.

Private Sub Workbook_Open()
For Each q In ThisWorkbook.Connections
q.Refresh
DoEvents
Next
End Sub
Svein Arne Hylland
  • 73
  • 6
  • Thanks for the input. The delay that I need relates to recalculations when a slicer is changed, not a query refresh. Even with Enable Background Refresh set to false, the VBA barrels right past that slicer selection while PowerPivot is still trying to get data and finish calculating. – OldKingCole Dec 12 '22 at 15:22
  • Thanks for asking this question. Ran into same issue myself now when I moved away from flat file sharing to use Power BI cubes. Thanks for sharing a working answer. – Svein Arne Hylland Dec 14 '22 at 12:01