Forum Discussion

afifhazim's avatar
afifhazim
Copper Contributor
Feb 17, 2023

Automate query schedule refresh in Excel while file is not open

Hi, is there a way to automate query refresh in Excel while not opening the file?

excel
office 365

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 17, 2023

    afifhazim 

    Press Alt+F11 to activate the Visual Basic Editor.

    Double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left hand side.

    Copy the following code into the ThisWorkbook module:

    Private Sub Workbook_Open()
    Me.RefreshAll
End Sub

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook (*.xlsm),

    Make sure that you allow macros when you open it.

    • afifhazim's avatar
      afifhazim
      Copper Contributor
      Feb 18, 2023

      Hi HansVogelaar ,

      Thank you for your reply. Unfortunately your suggestion does not meet my requirements. I can also trigger my query to automatically refresh when I open the file through the query properties like in the screenshot below. 


      But what I am looking for is to automate query refresh without even opening the file. I am not sure if it is possible or not. 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Feb 18, 2023

        afifhazim 

        If you are on business subscription and query source from external data, you may load query to data model (even if you don't use it), upload file on Power BI workspace and schedule refresh here.

Resources