Disable automatic updates of excel file

%3CLINGO-SUB%20id%3D%22lingo-sub-3091331%22%20slang%3D%22en-US%22%3EDisable%20automatic%20updates%20of%20excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3091331%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20file%20that%20automatically%20updates%20from%20different%20data%20connections%20(SQL)%20when%20users%20open%20it.%3C%2FP%3E%3CP%3EHowever%20I%20want%20to%20save%20an%20static%20copy%20that%20does%20not%20update%20to%20latest%20data%20when%20it%20is%20opened.%20How%20do%20I%20do%20this%3F%20I%20tried%20to%20disable%20data%20connections%2C%20but%20I%20want%20it%20to%20be%20only%20for%20the%20static%20copy%20not%20a%20general%20Excel%20setting.%20Hope%20you%20can%20help%20me.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3091331%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3091453%22%20slang%3D%22en-US%22%3ERe%3A%20Disable%20automatic%20updates%20of%20excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3091453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1292027%22%20target%3D%22_blank%22%3E%40Kasper22%3C%2FA%3E%26nbsp%3BConnecting%20as%20in%20Power%20Query%3F%20If%20so%2C%20you%20can%20set%20the%20query%20options%20to%20not%20refresh%20when%20opening%20the%20file%2C%20by%20unchecking%20the%20relevant%20box.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_0-1643622398486.png%22%20style%3D%22width%3A%20368px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343856i008AF287B221E9E4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_0-1643622398486.png%22%20alt%3D%22Riny_van_Eekelen_0-1643622398486.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3091932%22%20slang%3D%22en-US%22%3ERe%3A%20Disable%20automatic%20updates%20of%20excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3091932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EYes%20in%20Power%20Query.%20So%20according%20to%20your%20suggestion%20the%20process%20would%20be%3A%3CBR%20%2F%3E1.%20Open%20Excel%20file%20and%20it%20will%20update%20automatically%3CBR%20%2F%3E2.%20Save%20a%20copy%20of%20the%20excel%20file%20and%20uncheck%20the%20automatically%20refresh%20from%20all%20power%20query%20connections%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20problem%20here%20is%20that%20if%20users%20somehow%20go%20to%20Data%20%26gt%3B%20Refresh%20it%20will%20still%20refresh%20the%20file%20that%20was%20meant%20to%20be%20static.%20Can%20it%20somehow%20be%20saved%20as%20a%20workbook%20with%20static%20data%20instead%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

7 Replies

@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.

Riny_van_Eekelen_0-1643622398486.png

 

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

@Kasper22 

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

image.png

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

Thank you, but I can not locate above view in my Excel?
I have 7 queries in the Excel sheet that are used by 30+ pivottables.

@Kasper22  What are your query load options, you load query to data model only ?

Yes the queries are loaded only to the data model

@Kasper22 

When simply disable all refresh options, includes Refresh All, in the final file.