refresh excel every morning

[SQL-Power]

Hello everyone,

i try the following.

A workflow that every morning open my excel file, refreshed it, saves and close again.

What would the process look like? 🤗

Thank you in advance

[Dudemaster]

The file must be saved as a macro enabled file, - by your screen shot, your file extension as showing as regular XLSX file as opposed to the XLSM extension that is appended to the file when you save-as a macro enabled excel file. For the edification of others, the file can not sit on a desktop, - PowerAutomate requires access to the file so it must be online such as OneDrive or SharePoint. 

[takolota]

@SQL-Power 

I have seen some blog posts on setting up a file with a Macro that auto-refreshes the data each time someone opens the file.

https://www.thespreadsheetguru.com/blog/vba-refresh-data-connections-excel?sales_kw=excel%7Csales_kw...

But if that doesn’t work for your use-case, then there is this Excel batch create & update template you could try

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...

[pradeep_khanna]

@SQL-Power, You don't need Macros or scripting for achieving this: There is a property for external data connections: "Refresh data when opening the File": turn that on. Right-click on the table that carries the external data, and proceed as per the pics below

[AspasiaLiz]

I have similar problem but people who are opening the files do not have access to the data directly so they cannot refresh the data-?

[takolota]

@AspasiaLiz 

If you want to run the refreshes automatically & most users don’t have access to the data source, then you can try adjusting this batch create & update template to your datasource:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...

[Alichson]

This articles states that Refresh in Office Scripts is not supported by Power Automate.  The scripts run successfully, but they do nothing and throw no errors.

https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting

I have also adopted a workaround using Power Automate to add / update rows of an Excel table when items are added / updated to a SharePoint List. The Flow that I am using described in this video.

https://www.youtube.com/watch?v=xwvLGbyhssI

However, I also have SharePoint items being deleted and I could not create a similar flow to remove rows when items are deleted because the trigger "When an item is deleted" can only be used by admins.

https://learn.microsoft.com/en-gb/connectors/sharepointonline/#when-an-item-is-deleted

I have not found a workaround, so I am just periodically opening the file and refreshing the connection manually for now.

[PvO31]

Thanks @Alichson for confirming Office scripts with refreshes don't work in Power Automate. Mine are not even able to run sucessfully.

So I'm wondering how @RemcoV16 managed to pull this off. Unfortunatly he hasn't responded since.

[Leafar30]

Thats good if you want to open the file. What about if you dont want to open the file but you want that file to be updated?

[pradeep_khanna]

If you want the candy, you got to open the jar 😊

[rinkymaghnani]

I have similar query. I have created a power Automated flow which send emails to employees on work anniversary. This flow is connected to an excel saved on my one Drive. That excel has a column with today's Date updated by Function- Today(). The emails are sent to those whose anniversary dates matches with Today's Date. For this the excel needs to be opened, refreshed and saved. 

My Question- Can the today() function be refreshed everyday without opening the Excel.

[alynadh97]

Hi, can you please show us how you set the Flow ?

[Nic-AF]

See Alichson's reply above.

Specifically, this video https://www.youtube.com/watch?v=xwvLGbyhssI

[Kinson-Lam]

Hi, I experienced the same problem.  My case is trying to refresh all data for my Power Query by using Workbook.refreshAllDataConnections in Office script.  However, it doesn't work in Power Automate environment.  After researched, this method is not going to work.  Microsoft published an Article to mention that refreshAllDataConnections or other refresh data function do not work in Power Automate environment.  It is a dead-end.  Sad.😫

Troubleshoot Office Scripts running in Power Automate - Office Scripts | Microsoft Learn