VBA script to run power query + datamodel load and save separate files

[Papermain]

Hello,

Currently i am working on a dashboard within Excel, which uses data in the data-model as a source. There is data for multiple countries in the data and for GDPR reasons, i need to split the main file into separate files for each country. Normally i do this with VBA + PQ by adding a drop-down source for a table with just the country. Then based on this value, i will inner join the main data so it will only load the data for the specific country. Then the VBA script loops through each country value, and saves it in separate files. The trick to getting this to work is disabling background refresh in the query preporties, so the vba will only loop to the next country value once the query is done loading. 

Hower, because the data is loaded to the data-model the "enable background refresh" is greyed out and i cannot interrect with it. Now when i run the VBA script, it will generate 11 files with all of the same country data in it, because it starts saving the files even if the pq refresh hansn't finished.

Any tips on how to adjust the vba script to make this work? Current VBA script

Sub Save_per_country()
    Application.ScreenUpdating = False
    Dim Country_list As Range, wsCountry As Worksheet, wb As Workbook, ws As Worksheet
    Set wsCountry = Sheets("Drop-Down")
    
    For Each Country In wsCountry.Range("A2:A3")
        If Worksheets("QueryParameters").Visible = xlSheetVeryHidden Then
        Worksheets("QueryParameters").Visible = xlSheetVisible
        End If
        If Worksheets("drop-down").Visible = xlSheetVeryHidden Then
        Worksheets("drop-down").Visible = xlSheetVisible
        End If
        Sheets("QueryParameters").Select
        Range("A2") = Country
        ThisWorkbook.RefreshAll
        Worksheets("QueryParameters").Visible = xlSheetVeryHidden
        Worksheets("drop-down").Visible = xlSheetVeryHidden
        ActiveWorkbook.SaveCopyAs Filename:="H:\HR SC\HR SC Nederland\Rapportages\Standard reports\INT - diversity dashboard\ContryFiles\" & "Diversity overview" & Country & ".xlsm"
    
    Next Country
  
    
End Sub

[MAwwad]

One possible approach is to use a loop that checks the query status before moving on to the next country. You can add a loop to check if the data refresh has completed before saving the file. Here's an example:

For Each Country In wsCountry.Range("A2:A3") If Worksheets("QueryParameters").Visible = xlSheetVeryHidden Then Worksheets("QueryParameters").Visible = xlSheetVisible End If If Worksheets("drop-down").Visible = xlSheetVeryHidden Then Worksheets("drop-down").Visible = xlSheetVisible End If Sheets("QueryParameters").Select Range("A2") = Country ThisWorkbook.RefreshAll ' Loop until the data model refresh is complete Do Until ThisWorkbook.Model.DataModelState = xlModelDataModelStateReady DoEvents Loop Worksheets("QueryParameters").Visible = xlSheetVeryHidden Worksheets("drop-down").Visible = xlSheetVeryHidden ActiveWorkbook.SaveCopyAs Filename:="H:\HR SC\HR SC Nederland\Rapportages\Standard reports\INT - diversity dashboard\ContryFiles\" & "Diversity overview" & Country & ".xlsm" Next Country

In this modified script, the loop waits until the data model refresh is complete by checking the DataModelState property of the workbook's Model object. The loop uses the DoEvents statement to allow Excel to process other events while it waits for the refresh to complete. Once the refresh is complete, the script proceeds to save the file and move on to the next country.

This approach should ensure that each file contains data only for the selected country, without running into the issue of saving files before the data refresh is complete.

[Papermain]

Hello,

Thanks for the reply. When i paste the VBA it gives me errors. Would it be possible for you to copy your code in the code formatting way so i can copy/paste it?

Thanks!