Forum Discussion

Kasper22's avatar
Kasper22
Copper Contributor
Jan 31, 2022

Disable automatic updates of excel file

Hi,

I have an Excel file that automatically updates from different data connections (SQL) when users open it.

However I want to save an static copy that does not update to latest data when it is opened. How do I do this? I tried to disable data connections, but I want it to be only for the static copy not a general Excel setting. Hope you can help me. Thanks!

BI & Data Analysis
excel
  • Gian1959's avatar
    Gian1959
    Copper Contributor
    Aug 25, 2022
    Hello! I hope you still need this and that it helps. I've found the same workaround in Excel 2010 (!) and 2019. (I'm using an Italian version so I'll try to translate the corresponding English items)

    __with no worksheet open__ select menu File _Options _Advanced settings
    Then scroll until the end at "General" (group of items) and DE-select "Ask to update automatic links" (or similar wording) then hit "OK".
    Once you have done that you can open your excel file WHILE HOLDING DOWN THE SHIFT KEY.
    This will hopefully present you with the file as you last saved it (a static copy) without any update via SQL. Of course any subsequest manual update will give you an updated version of the file. And you may also need to unflag auto update every X minutes for that file.
    Holding down SHIFT does not work if you don't do the previous steps, at least that's what I found.
    Please note: I don't know if the above flag in Options is needed elsewhere.

    • Gian1959's avatar
      Gian1959
      Copper Contributor
      Aug 25, 2022
      The SHIFT trick is an option, i.e. the same file, as well as ALL your other files, will continue to auto update if do NOT use SHIFT on launching the file/s.
      Regards
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 31, 2022

    Kasper22 Connecting as in Power Query? If so, you can set the query options to not refresh when opening the file, by unchecking the relevant box.

     

    • Kasper22's avatar
      Kasper22
      Copper Contributor
      Jan 31, 2022

      Riny_van_EekelenYes in Power Query. So according to your suggestion the process would be:
      1. Open Excel file and it will update automatically
      2. Save a copy of the excel file and uncheck the automatically refresh from all power query connections

       

      The only problem here is that if users somehow go to Data > Refresh it will still refresh the file that was meant to be static. Can it somehow be saved as a workbook with static data instead?

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jan 31, 2022

        Kasper22 

        You may right click on returned by Power Query table and disconnect table from it.

        Table will be disconnected forever and Power Query will be shifted to Connection only mode.

Resources