Forum Discussion

Dan44's avatar
Dan44
Helper I
2 years ago

Auto Refresh Excel Report with Power Pivot Data Model

Hi all,   I have this Office Script which can be scheduled in Excel:    function main(workbook: ExcelScript.Workbook) { // Refresh all data connections workbook.refreshAllDataConnections...
Need Help
JGroothedde's avatar
JGroothedde
Resolver II
2 years ago

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's avatar
    Dan44
    Helper I
    2 years ago

    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's avatar
      JGroothedde
      Resolver II
      2 years ago

      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's avatar
      JGroothedde
      Resolver II
      2 years ago

      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's avatar
        Dan44
        Helper I
        2 years ago

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