Forum Discussion

IB_rewoP's avatar
IB_rewoP
Frequent Visitor
1 year ago
Solved

Auto Refresh Power Query in Excel with Web Content Connection

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.   

 

  

Need Help
  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    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 

     

12 Replies

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    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's avatar
      IB_rewoP
      Frequent Visitor
      1 year ago

      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's avatar
        louismaronese
        New Member
        1 year ago

        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" ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

     

    --Nate

    • IB_rewoP's avatar
      IB_rewoP
      Frequent Visitor
      1 year ago

      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? 

  • uzuntasgokberk's avatar
    uzuntasgokberk
    Super User
    1 year ago

    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. 

    Kind Regards,
    Gökberk Uzuntaş

    📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

    🔗 Stay Connected:
    📘 Medium |
    📺 YouTube |
    💼 LinkedIn |
    📷 Instagram |
    🐦 X |
    👽 Reddit |
    🌐 Website |
    🎵 TikTok |

    • IB_rewoP's avatar
      IB_rewoP
      Frequent Visitor
      1 year ago

      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's avatar
        uzuntasgokberk
        Super User
        1 year ago

        Hello IB_rewoP ,

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

        Kind Regards,
        Gökberk Uzuntaş

        📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

        🔗 Stay Connected:
        📘 Medium |
        📺 YouTube |
        💼 LinkedIn |
        📷 Instagram |
        🐦 X |
        👽 Reddit |
        🌐 Website |
        🎵 TikTok |

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    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.

  • IB_rewoP's avatar
    IB_rewoP
    Frequent Visitor
    1 year ago

    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