Auto Refresh Excel Report with Power Pivot Data Model

[Dan44]

Hi all,

I have this Office Script which can be scheduled in Excel: 

function main(workbook: ExcelScript.Workbook) {
    // Refresh all data connections
    workbook.refreshAllDataConnections();
}

This works well when my Excel report has been created through Analyze in Excel, from a semantic model published in Fabric. The big issue is when I have reports are configured by pulling data through Power Query for a Power Pivot data model. The scheduled refresh of the above script will not exectute (but the script will execute if I manually run it).

I therefore tried to amend the script to first refreshes all data connections and then introduces a delay before refreshing the Power Pivot data model:

function main(workbook: ExcelScript.Workbook) {
    // Refresh all data connections
    workbook.refreshAllDataConnections();

    // Delay in minutes
    const delayInMinutes = 3;
    const totalDelay = delayInMinutes * 60 * 1000; // Convert to milliseconds

    // Give sufficient delay to ensure connections are being refreshed properly
    setTimeout(() => {
        // Refresh the Power Pivot data model
        workbook.getModel().refresh();
    }, totalDelay); // 3 minutes delay
}

This doesn't work either however.

I then thought it could be because of the prompts I get from my workbook of: SECURITY WARNING External Data Connections have been disabled. So I added the folder containing your workbook to the list of trusted locations in Excel (File > Options > Trust Center > Trust Center Settings > Trusted Locations). Nothing changed with this either. 

Is there any way to overcome this? All I need is a reliable way to shedule refresh Power Query in my Power Pivot enabled workbook.

Thanks in advance

[SaiTejaTalasila]

Hi @Dan44 ,

You can check this it might be helpful.

https://www.ablebits.com/office-addins-blog/excel-power-query-tutorial/#:~:text=If%20you%20need%20Po....

Thanks,

Sai Teja 

[JGroothedde]

Hi @Dan44 , apart from refreshing the datamodel, did you try also refreshing the pivot table to ensure the data hasn't been refreshed?

	// Refresh Pivot Tables
	workbook.refreshAllPivotTables();

This, combined with refreshing the Data connections works for me, just tested this.

[Dan44]

@JGroothedde thanks for getting back. When you say pivot table I am instead using Power Pivot from the data model. So I am trying to refresh the power pivot enabled Excel report sourcing data through Power Query, with the following:

function main(workbook: ExcelScript.Workbook) {
    // Refresh all data connections
    workbook.refreshAllDataConnections();

    // Refresh all pivot tables
    workbook.refreshAllPivotTables();
}

However still no luck (when scheduling the script but works fine when manually running it). Can you please let me know how you were able to schedule refreshes yourself?

[JGroothedde]

Hi @Dan44 unfortunately i haven't gotten around testing it yet, will probably have some time to get into this next week. Have a good weekend for now!

[Dan44]

@JGroothedde hi again just wondered if you had any suggestions? Unfortunately is a tricky one! Thanks

[JGroothedde]

Ah sorry i think i misunderstood. Let me check and see this afternoon if i can get it to work. It's an interesting problem! 🙂 Will get back to you!

[JGroothedde]

Hi @Dan44 , i just saw your comment in the older topic. I think i've encountered this before (and found a fix for it too). I will check and see if i can find it for you!

[Anonymous]

Hi,@Dan44 . I am glad to help you.
1. You can try to use Power Automate to create a new stream to periodically refresh the data in the Excel file
url:Get started with Power Automate - Power Automate | Microsoft Learn
Official Microsoft Power Automate documentation - Power Automate | Microsoft Learn

2. You can also try using VBA macros to automatically refresh the Power Query and Power Pivot data models instead of your original Office Script.

You can also go to other forums for more help:
url:
Welcome to the Excel Community - Microsoft Community Hub
Excel (microsoft.com)
Best wishes for solving your problem sooner rather than later
I hope my suggestions will bring you some good ideas.

If you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dan44]

@Anonymous thank you but unfortunately I have been struggling with both of the above methods, when it comes to a Power Pivot enabled workbook sourcing data through Power Query