cancel
Showing results for 
Search instead for 
Did you mean: 

Add in Excel Flow the ability to refresh all pivottables and links in an Excel Spreadsheet file

To add an action to enable once say a new row is added to a table as part of the flow to the have a subsequent action of refreshing all pivot tables / pivot charts.

 

In addition a further action to refresh all external links to an excel spreadsheet file.

 

Benefit - auto refresh action to be added as the next step after a table has a new row added or an existing row updated so a related pivot table / pivot chart is refreshed.

 

Best regards

 

Mike H

 

Mike H

Status: New
Comments
Advocate II

Is there a workaround to this now? We can't use macro enabled excel files with flow. How do I do this? I just need the pivot tables updated automatically!

Regular Visitor

@KProuty 
Currently .xlsm files cannot be used with Forms / Flows.   Fingers crossed this will be sorted.

In the meantime if you have a Form and you want to analyse the responses with pivot tables automatically I have a workaround.

If you would like to discuss please let me know your LinkedIn profile details and I will connect with you.

New Member

Good idea to have the pivot table refresh action add in.

I'm currently stuck at here!!!

 

Microsoft - pls help!

Regular Visitor

OMSunway

There is a workaround I have come up with.

Create a separate data / pivot table file and then write an auto open macro which when you open the analyser file it opens the linked form file and copies excel table into a sheet in the analyser file and then refreshes pivots.   Little bit more involved than this simple explanation but is doable.  Use this several times.

Hope this helps you.

 

Mike H

New Member

Yes please, I tried using the script trigger in power automate.  But no dice, no error, it looks like it triggers the script fine but it does not actually run it. 

 

In case you are not familiar with the script ability.  An xlsx file does not allow macro (VBA) when it is on prem but up in excel (online) you can add VBA script to xlsx files that store in a SharePoint or onedrive.   Then, you can trigger that script from power automate.  This was my refresh script below. 

 

function main(workbook: ExcelScript.Workbook) {
    // Refresh all data connections 
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.refreshAllPivotTables();
}
 
It runs and does not error, but does not refresh either.   frustrating. 
 
 
Update 1:  When I open the excel file it shows the following message:  
 
REFRESH PAUSED  More than one person is editing, so "Refresh data when opening" and "Refresh every..." were paused. Click Refresh on the Data tab to refresh for everyone.
 
So I think power automate is hanging the file open after I add a row to the table for the pivot table to trigger.   I'll keep working on it and let you know.
 
 
 
Update 2:  GOT IT WORKING!
This might get lengthy.
1)  Save the excel xlsx file to one drive or sharepoint and make sure you do not keep a local copy that is syncing.  If you are keeping a local copy of the report it will prevent the refresh because it cause a lock:  see step 4: https://support.microsoft.com/en-us/office/excel-file-is-locked-for-editing-6fa93887-2c2c-45f0-abcc-...
 
2) in excel online, open the xlsx file recommend going to file\info and protect the sheet.  This set the sheet to view mode when opening, as an added precaution.  
 
3) in excel online (this only works in online and the file has to be an xlsx and not a xlsm file), click on "Automate" and "code editor."
 
4) put this code in:

function main(workbook: ExcelScript.Workbook) {
    // Refresh all data connections 
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.refreshAllPivotTables();
}
 
5) make sure you checkmark the "share with others in the workbook" so power automate can see the script.
 
6) in power automate, do the excel online (business), trigger script connector.   Select your script and done!
 
 
Wow, that took some troubleshooting, not too much documentation on this ability yet. 
 
 

  

New Member

Hi,

 

For those asking for this in Sharepoint, I find if you embed the pivot table or pivot chart in Sharepoint rather than use file viewer, when you open the SharePoint page these do get refreshed automatically.

 

You must though tick "Refresh data when opening the file" in the data tab within Pivot Options for each pivot table.

 

😀

Regular Visitor

Google Sheet doesnt even need a flow. A free service! It would be great if a paid service would perform better in most, if not all, aspects.