Auto Refresh Power Query in Excel with Web Content Connection

[IB_rewoP]

hi community,

I have a power query set up in excel that has a connection to web contents and I have set the query to refresh every 6 minutes and checked the enable background refresh.  Also the query loads to a table in Sheet1.

After saving this Excel file and closing it, I've opened the file multiples times but the data in the table in Sheet1 aren't refreshed.  I still have to manually click the refresh icon next to the query in order to see the values in the table change. 

So what am I doing wrong?  I thought the Auto Refresh and Enable Background Refresh will refresh the query AND load the new data into the table?  Is there an addition step I need to do or set up in order for the freshed query's data to populate the table?

Below is my web content connection which works with no issue when I manually refresh the query by clicking on the refresh icon.   

[IB_rewoP]

@Poojara_D12 

Did as you said by saving the Excel as a xlsm file with the VBA codes and using PA to refresh the file and it worked.  However I am not sure if my organisation likes xlsm files.  I've also created another solution without VBA using Power Automate, for anyone interested sharing the video:  https://www.youtube.com/watch?v=4Qoh9t_ZvqI

[v-sgandrathi]

Hi @IB_rewoP ,
Thank you  for reaching out to the Microsoft Fabric Community forum.

If you select Refresh data when opening the file, the data will update automatically whenever you open the file. If you set a timer using Refresh every 6 minutes, Excel will refresh the data at those intervals as long as the file is open.

However, this feature will not function if the Excel file is closed.

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more easily.

Best Regards,
Sahasra.

[Omid_Motamedise]

Such feature doesnt work if you close your Excel file.

[Anonymous]

That article was talking about not having to open the source file to refresh your current file. if you have the file open, you can refresh it every so many minutes, without having to click refresh and without having to open the source file. They're not saying that your data tables will update while the file is closed. I've never read that--even with VBA you have to open the file.

--Nate

[uzuntasgokberk]

Hello @IB_rewoP ,

I assume that you are using excel. So that when ever you open the excel the settings of "Refresh data when opening the file" needs to be enabled. 

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

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

[IB_rewoP]

no, I didn't want someone to keep having to open the Excel spreadsheet to refresh the data. 

My understanding is that checking the Enabling background Refresh mean I do need to have the Excel spreadsheet open as the datasource will refresh automatically - correct?  

At the moment, I have to keep the Excel spreadsheet open all the time in order for the data source to refresh.

[uzuntasgokberk]

Hello @IB_rewoP ,

İf you enable "Refresh Every" option, yes it will be refreshed.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

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

[Anonymous]

Try unchecking "Enable background refresh"--at least that works when your pivot tables won't update after the query updates.

--Nate

[IB_rewoP]

Auto refresh works but only if I have Excel open.  There are so many blog and written saying that it is not necessary to have Excel open all the time in order to have background auto refresh to work? 

[Poojara_D12 · Accepted solution]

Hi @IB_rewoP 

• Why It Doesn’t Work:

  • Auto-refresh only works when the Excel file is open. Closing the file stops the refresh.
  • "Enable Background Refresh" only allows Excel to continue working while refreshing but doesn’t apply when the file is closed.

• Solution:

  • Use VBA: Add this code to refresh when the file opens:

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
End Sub
​

• Save the file as a macro-enabled workbook (.xlsm).
• Power Automate: Use it to refresh the file if stored in OneDrive/SharePoint.
• Query Properties: Right-click the query in Data > Queries & Connections, select Properties, and check:
  • "Refresh every X minutes"
  • "Refresh data when opening the file."

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[IB_rewoP]

If background auto refresh does not work without having Excel open then why are there so many blog that written about it.  This blog going back in Apr-24: https://www.ablebits.com/office-addins-blog/excel-power-query-tutorial/

I will give the suggested work around a try, any good videos on how to create Power Automate to refresh Excel?

Keep to here others about background auto refresh without having Excel open all the time.

[louismaronese]

Do we have an update on the final solution ?

Is the Excel file refreshed, even if closed,  if we set the Query Properties (Right-click the query in Data > Queries & Connections) "Refresh every X minutes" ?