SOLVED
Home

Can no longer refresh data in a Protected Workbook since Excel version 1803

%3CLINGO-SUB%20id%3D%22lingo-sub-179229%22%20slang%3D%22en-US%22%3ECan%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-179229%22%20slang%3D%22en-US%22%3E%3CP%3ESeems%20to%20have%20been%20a%20functionality%20change%20introduced%20in%20version%201803.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20have%20a%20%3CSTRONG%3Eprotected%20workbook%3C%2FSTRONG%3E%20with%20some%20data%20connections%20to%20pull%20in%20data%20from%20SQL.%20Since%20updating%20to%20version%201803%20the%20message%20'Workbook%20is%20protected%20and%20cannot%20be%20changed'%20appears%20when%20trying%20to%20refresh%20the%20data.%20Removing%20workbook%20protection%20allows%20data%20to%20be%20refreshed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsing%20the%20same%20workbook%20in%20version%201802%20allows%20the%20data%20to%20be%20refreshed%20with%20the%20workbook%20protection%20left%20on.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPosting%20here%20in%20case%20anyone%20else%20experiences%20this%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-179229%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291176%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239867%22%20target%3D%22_blank%22%3E%40Michael%20Stephenson%3C%2FA%3E%2C%20I'd%20like%20to%20suggest%20that%20you%20speak%20with%20your%20IT%20on%20this%20topic%20then.%26nbsp%3BRe-install%26nbsp%3Bshould%20probably%20solve%20the%20issue%20for%20you.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy%3C%2FP%3E%0A%3CP%3E-%20Excel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291115%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291115%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%2C%20unfortunately%20my%20Office%20updates%20are%20managed%20by%20the%20system%20admin%20and%20I%20cannot%20update%20Office%20until%20they%20schedule%20updates%20for%20the%20entire%20group.%20I%20do%20not%20know%20when%20these%20will%20be%20scheduled.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Mike%20S%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290875%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290875%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239867%22%20target%3D%22_blank%22%3E%40Michael%20Stephenson%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20please%20upgrade%26nbsp%3Byour%20Office%20version%3F%20This%20should%20be%20resolved%20in%20the%20latest%20semi-annual%26nbsp%3BOffice%20build%20version%201808%20(build%2010730.xxxxx).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy%3C%2FP%3E%0A%3CP%3E-%20Excel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-285859%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-285859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239867%22%20target%3D%22_blank%22%3E%40Michael%20Stephenson%3C%2FA%3E%2C%20I%20am%20checking%20this.%26nbsp%3BIn%20a%20meantime%20please%20un-protect%20the%20sheet%20as%20a%20workaround.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283973%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283973%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20still%20running%20into%20the%20same%20problem.%20I%20am%20running%20Excel%20version%201803%2C%20build%209126.2295%20(see%20below).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59304iDF356B4B27799BCF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BI%20am%20running%20a%20basic%20query%20between%20two%20Excel%20workbooks%20on%20the%20same%20network.%20When%20the%20sheet%20I%20want%20to%20update%20is%20protected%20(default%20protection%20settings)%2C%20I%20receive%20the%20below%20error.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20863px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59305i537ED4662E84E0B6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20to%20clarify%20that%20I%20receive%20the%20error%20message%20when%20selecting%20%22Refresh%20All%22%2C%20in%20case%20that%20wasn't%20clear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMike%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-218596%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-218596%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEasy%20fix.%20Just%20protect%20sheet%20and%20workbook%20with%20new%20password%20of%20ur%20choice%20and%20then%20unprotect%20again...should%20be%20all%20sorted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201756%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201756%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20David%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20not%20sure%20I%20understand%20your%20scenario.%20Are%20you%20saying%20that%20you%20have%20a%20query%20that%20cannot%20be%20refreshed%20with%20an%20older%20Excel%20version%20anymore%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20please%20provide%20step-by-step%20instructions%20for%20us%20to%20repro%20the%20problem%20locally%3F%20I%20will%20also%20appreciate%20if%20you%20can%20share%20a%20problematic%20workbook%20with%26nbsp%3Bme%3F%20Just%20make%20sure%20to%20remove%20or%20scramble%20all%20the%20sensitive%20or%20private%20data%20before%20sharing%20your%20workbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201360%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201360%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guy%2C%3C%2FP%3E%3CP%3EThanks%20for%20the%20fix%2C%20however%20it%20now%20appears%20something%20else%20has%20changed%20to%20do%20with%20queries.%20In%20our%20documents%20we're%20using%20we%20have%20an%20Excel%20file%20connection%20that%20pulls%20some%20data%20in%20that%20is%20centrally%20updated%2C%20in%20versions%20up%20to%20the%20latest%20this%20seemed%20to%20work%20fine.%26nbsp%3BNow%20it%20appears%20the%20latest%20version%20of%20Excel%20won't%20allow%20us%20to%20refresh%20that%20connection%20and%20asks%20for%20a%20Microsoft%20Access%20Database%20Engine%20OLE%20DB%20Initialization%20Information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20replacing%20the%20query%20using%20the%20latest%20version%20of%20Excel%2C%20and%20it%20works%20fine%2C%20until%20you%20try%20opening%20the%20document%20with%20an%20older%20version%20of%20Excel%20and%20I%20get%20this%20error%3A%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F35490i878BC446BB1F7906%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20for%20a%20fix%3F%20I%20seem%20to%20have%20one%20version%20that%20works%20on%20older%20versions%20(e.g.%202010)%20and%20one%20that%20works%20with%20the%20new%20version%20of%20Excel%20365%20v1805%20build%209330.2087.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDavid%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-200986%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-200986%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20update%20Guy.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-200541%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-200541%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20fix%20was%20deployed%20for%20the%20following%20Office%20versions%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EVersion%201803%20(Build%209126.2196)%20and%20later%3C%2FLI%3E%0A%3CLI%3EVersion%201804%20(Build%209226.2135)%20and%20later%3C%2FLI%3E%0A%3CLI%3EVersion%201805%20(Build%209330.2017)%20and%20later%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EHope%20this%20helps.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy.%3C%2FP%3E%0A%3CP%3E-%20Excel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-198397%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198397%22%20slang%3D%22en-US%22%3EIt%20seems%20to%20be%20working%20now%20(Version%2016.0.9226.2156).%20Thank%20you%20for%20letting%20us%20know.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-198132%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198132%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20problem%20seems%20to%20be%20solved%20now.%20I%20don't%20get%20the%20pop-up%20anymore%20when%20I%20open%20the%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20the%20same%20for%20you%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3CP%3ENicolaj%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-196172%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-196172%22%20slang%3D%22en-US%22%3E%3CP%3EAbove%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%26nbsp%3Bsuggested%20that%20it%20isn't%20a%20feature%20and%20the%20team%20is%20working%20on%20a%20fix.%26nbsp%3B%20%26nbsp%3BGuy%20-%20do%20you%20have%20any%20update%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%2C%3C%2FP%3E%3CP%3EDavid%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-196147%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-196147%22%20slang%3D%22en-US%22%3E%3CP%3ESame%20problem%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it's%20a%20feature%20and%20not%20a%20bug%20(which%20I'm%20really%20hoping%20is%20not%20the%20case)%20then%20I'd%20like%20an%20option%20to%20not%20use%20it.%20I%20honestly%20can't%20see%20what%20benefit%20would%20there%20be%20to%20having%20it%20work%20like%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-194833%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-194833%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20exact%20same%20problem.%3C%2FP%3E%3CP%3EI%20hope%20a%20solution%20will%20be%20found%20soon.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-192204%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-192204%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E-%20many%20thanks%20for%20your%20update.%3C%2FP%3E%3CP%3EI%20did%20finally%20receive%20an%20update%20to%20the%20ticket%20I%20logged%20through%20O365%2C%20although%20the%20information%20directly%20contradicts%20what%20Guy%20has%20said%20in%20this%20thread!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%5BTicket%20%23%3A7973974%5D%3C%2FP%3E%3CP%3EHello%20Alex%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3EThis%20issue%20is%20by%20design%20and%20even%20Out%20of%20Support%20for%20Office%20365%20Technical%20Support.%3C%2FSTRONG%3E%3CBR%20%2F%3EYou%20may%20share%20your%20valuable%20feedback%20for%20this%20on%20the%20below%20support%20links%20-%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F32287204-enable-data-query-refresh-with-powerpivot-tables%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F32287204-enable-data-query-refresh-with-powerpivot-tables%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F34126504-sql-queries-won-t-refresh-on-protected-workbook-si%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F34126504-sql-queries-won-t-refresh-on-protected-workbook-si%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EShould%20you%20have%20any%20additional%20queries%2C%20please%20let%20me%20know.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3EAnkit%20Shukla%3CBR%20%2F%3EMicrosoft%20Office%20365%3C%2FP%3E%3CP%3ECwsRequestId%3A7438ad61-ee11-4d5f-a9de-af58cf7fa14a%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-190048%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-190048%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Guy!%20%3A)%20Please%20keep%20us%20updated.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDavid%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20a%20known%20regression%20and%20we%20are%20working%20in%20a%20fix.%20Stay%20tuned%20and%20follow%20up%20the%20updates%20on%20Tech%20Community.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGuy%3CBR%20%2F%3E-%20Excel%20Team%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-190044%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-190044%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20a%20known%20regression%20and%20we%20are%20working%20on%20a%20fix.%20Stay%20tuned%20and%20follow%20up%20the%20updates%20on%20Tech%20Community.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy%3CBR%20%2F%3E-%20Excel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189789%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189789%22%20slang%3D%22en-US%22%3E%3CP%3EI%20spoke%20to%20a%20microsoft%20representative%20for%202%20hours%2C%20that%20was%20asigned%20via%20our%20administrator%2C%20no%20resolution%20at%20all...then%20just%20got%20pointed%20to%20another%20support%20group%20on%20the%20internet%20that%20i%20can%20not%20join...is%20getting%20rather%20frustrating!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189398%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189398%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20get%20a%20ticket%20raised%20on%20O365%20support%2C%20but%20as%20I'm%20not%20the%20company%20administrator%20it's%20proving%20irritating.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-188738%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188738%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20has%20created%20MAJOR%20issues%20for%20me.%20I%20have%20loads%20of%20protected%20models%20and%20reports%20that%20extract%20info%20via%20SQL%20that%20are%20business%20critical.%20Please%20microsoft%2C%20can%20you%20look%20at%20this%20this%20problem%20ASAP%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187307%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187307%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20frustrating%2C%20I've%20reached%20out%20on%20Twitter%20again%20to%20the%20social%20team%20to%20see%20if%20they%20can%20chivvy%20something%20along%2C%20would%20suggest%20to%20do%20the%20same%20and%20raise%20more%20tickets%20about%20the%20same%20issue.%26nbsp%3B%20They%20may%20not%20do%20anything%20unless%20there's%20enough%20noise.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187301%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187301%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20just%20checked%20the%20ticket%20I%20had%20logged%20on%20this%20with%20O365%20support%20and%20it%20appears%20to%20have%20been%20closed%20without%20any%20response.%20Thanks%20Microsoft!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20from%20MS%20Support%20is%20looking%20at%20this%2C%20the%20ticket%20number%20is%20%237854947%2C%20please%20reopen%20and%20provide%20a%20response.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187300%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187300%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20a%20major%20problem.%20Everybody%20vote%20this%20thread%20up%20and%20send%20complaint%20to%20Microsoft%20helpdesk.%3C%2FP%3E%3CP%3ECoded%20workbooks%20are%20usually%20used%20by%20a%20lot%20of%20people%2C%20and%20then%20suddenly%20production%20halts.%20Not%20fun.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20problem%20with%20the%20solution%20to%20open%2Fclose%20workbook%3A%3C%2FP%3E%3CP%3EIf%20the%20code%20have%20to%20unprotect%2Fprotect%20for%20every%20data%20read%2C%20this%20takes%20time%20to%20run%20(since%20it%20decrypt%2Fencrypt%20the%20workbook)%2C%20making%20for%20a%20lesser%20user%20experience.%3C%2FP%3E%3CP%3EIf%20this%20is%20intentional%20by%20Microsoft%2C%20at%20least%20there%20should%20be%20an%20option%20to%20toggle%20the%20new%20functionality%20of%26nbsp%3B%20%22protect%20workbook%22%20on%2Foff.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-185032%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-185032%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20some%20code%20that%20I%20adapted%20for%20other%20purposes%20a%20while%20back.%20I%20have%20added%20ThisWorkbook.Unprotect%20and%20ThisWorkbook.Protect%20to%20it%20to%20get%20around%20the%20bug.%26nbsp%3B%20These%20are%20in%20events%20that%20are%20triggered%20before%20and%20after%20a%20refresh%20so%20you%20can%20have%20a%20background%20refresh%20and%20the%20workbook%20will%20only%20be%20protected%20again%20once%20the%20AfterRefresh%20event%20is%20triggered.%26nbsp%3B%20Use%20it%20at%20your%20own%20risk%2C%20but%20it%20is%20serving%20me%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20ThisWorkbook%20add%20the%20following%20declaration%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CPRE%3EDim%20QueryAnswer%20As%20clsQueryDone%3C%2FPRE%3E%3CP%3EThen%20add%20the%20following%20to%20the%20Workbook_Open%20event.%26nbsp%3B%26nbsp%3BMake%20sure%20to%20change%20the%20sheet%20name%20and%20query%20number%20to%20the%20correct%20ones.%26nbsp%3B%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Workbook_Open()%0A%20%20%20%20'Associate%20QueryAnswer%20with%20QueryTable%20%0A%20%20%20%20'Create%20query%20class%20object%0A%20%20%20%20Set%20QueryAnswer%20%3D%20New%20clsQueryDone%0A%20%20%20%20QueryAnswer.HookUpQueryTable%20Sheets(%22%3CSTRONG%3ESheet1%3C%2FSTRONG%3E%22).QueryTables(%3CSTRONG%3E1%3C%2FSTRONG%3E)%3C%2FPRE%3E%3CP%3E%26nbsp%3BSet%20up%20a%20Class%20Module%20called%20clsQueryDone%20and%20add%20the%20following%20to%20it%3A%3C%2FP%3E%3CPRE%3EPrivate%20WithEvents%20MyQueryTable%20As%20QueryTable%3C%2FPRE%3E%3CPRE%3EPrivate%20Sub%20MyQueryTable_AfterRefresh(ByVal%20Success%20As%20Boolean)%3CBR%20%2F%3E%20ThisWorkbook.Protect%20strMasterPwd%3CBR%20%2F%3E%20If%20Success%20Then%3CBR%20%2F%3E%20%20%20%20%20%20'Do%20whatever%20you%20want%3CBR%20%2F%3E%20Else%3CBR%20%2F%3E%20%20%20%20%20%20%20MsgBox%20%22There%20has%20been%20an%20error%20with%20the%20Query%20Refresh%22%2C%20_%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20vbOKOnly%20Or%20vbCritical%2C%20%22Refresh%20Error%22%3CBR%20%2F%3E%20End%20If%20%3CBR%20%2F%3EEnd%20Sub%26nbsp%3B%3C%2FPRE%3E%3CPRE%3EPrivate%20Sub%20MyQueryTable_BeforeRefresh(Cancel%20As%20Boolean)%3CBR%20%2F%3E%20ThisWorkbook.Unprotect%20strMasterPwd%3CBR%20%2F%3E'and%20do%20whatever%20you%20want%3CBR%20%2F%3EEnd%20Sub%26nbsp%3B%3C%2FPRE%3E%3CPRE%3EFriend%20Sub%20HookUpQueryTable(qt%20As%20QueryTable)%3CBR%20%2F%3E%20Set%20MyQueryTable%20%3D%20qt%3CBR%20%2F%3EEnd%20Sub%26nbsp%3B%3C%2FPRE%3E%3CP%3E%26nbsp%3BAndrew%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183030%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183030%22%20slang%3D%22en-US%22%3E%3CP%3EGlad%20I%20could%20help!%20It's%20not%20a%20perfect%20solution%20but%20at%20least%20it%20gives%20us%20the%20ability%20to%20still%20use%20workbooks%20in%20the%20same%20way%20for%20the%20moment.%20I've%20reached%20out%20to%20Microsoft%20in%20a%20couple%20of%20other%20ways%20as%20well%20to%20see%20if%20we%20can%20find%20out%20something%20more.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183015%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183015%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20David%20very%20much%20for%20the%20macro%2C%20it%20works%20again!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20encountered%20the%20same%20problem%2C%20my%20excel%20needs%20to%20download%20exchange%20rates%20and%20needs%20to%20be%20protected.%20Since%20update%20I%20can%20no%20longer%20have%20the%20workbook%20protected%20and%20refresh%20connections.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20using%20macro%20to%20refresh%20connections%2C%20it%20is%20necessary%20to%20disable%20refresh%20of%20connection%20on%20the%20background%20and%20to%20disable%20refresh%20of%20connection%20when%20opening%20the%20workbook%2C%20otherwise%20macro%20locks%20the%20workbook%20before%20connection%20is%20updated%20%2F%20shows%20message%2C%20that%20the%20workbook%20is%20protected%20and%20cannot%20be%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%20I%20would%20appreciate%20solution%20from%20Microsoft%20and%20if%20possible%20-%20without%20necessity%20to%20use%20macros%20at%20all.%20The%20last%20update%20is%20a%20real%26nbsp%3Bpain%20in%20the%20arse.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182688%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182688%22%20slang%3D%22en-US%22%3EI%20logged%20a%20call%20with%20Office%20365%20a%20few%20days%20ago%20to%20determine%20if%20this%20was%20a%20bug%20or%20an%20intended%20functionality%20change.%20Watch%20this%20space.%20I%20cited%20this%20thread%20in%20the%20ticket%20hopefully%20they%20are%20seeing%20people's%20frustration!!%20I%20will%20update%20this%20thread%20as%20I%20hear%20more.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182675%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182675%22%20slang%3D%22en-US%22%3E%3CP%3EAll%2C%3C%2FP%3E%3CP%3EI%20think%20if%20we%20want%20Microsoft%20to%20move%20and%20take%20some%20action%20to%20correct%20this%2C%20we%20need%20a%20lot%20more%20complaints%20in%20this%20thread%20than%20those%20of%20the%20few%20poor%20souls%20that%20are%20here%20now%2C%20who%20actually%20understand%20what%20is%20wrong.%20Suggest%20to%20mobilize%20your%20customers%20%2F%20colleagues%20to%20join%20in%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182615%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182615%22%20slang%3D%22en-US%22%3EYes%2C%20it%20should!%20I%20realised%20my%20mistake%20when%20I%20deployed%20it%20to%20a%20couple%20of%20our%20tools%20today.%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20isn't%20as%20elegant%20solution%20as%20I'd%20like%2C%20preferably%20I%20wouldn't%20want%20to%20have%20to%20implement%20a%20solution%2C%20but%20it%20does%20the%20job%20for%20the%20moment.%3CBR%20%2F%3E%3CBR%20%2F%3EDavid%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182573%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182573%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20David%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECorrect%20me%20if%20I%20am%20wrong%2C%20but%20shouldn't%20the%20last%20line%20in%20your%20RefreshQuery%20sub%20actually%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EThisWorkbook.Protect%20strAdminPassword%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182564%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182564%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20this%20is%20a%20big%20problem.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20query%20is%20refreshed%20using%26nbsp%3BVBA.%26nbsp%3B%20I%20use%26nbsp%3Ba%20class%20object%20for%20the%20query%20so%20it%20can%20run%20in%20the%20background%20and%20trigger%20events.%26nbsp%3B%20My%20workaround%20is%20to%20use%20the%20BeforeRefresh%20event%20to%20unlock%20the%20workbook%2C%20and%20the%20AfterRefresh%20event%20to%20lock%20it%20again.%26nbsp%3B%20I%20have%20not%20tried%20to%20see%20if%20these%20events%20are%20triggered%20when%20a%20user%20selects%20Refresh%20All%20from%20the%20data%20ribbon.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%20I%20do%20have%20to%20update%20all%20the%20workbooks%20that%20I%20have%20deployed!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-181742%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-181742%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Brian%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESure%2C%20happy%20to.%20I'm%20no%20Excel%20expert%20but%20put%20this%20together%20this%20afternoon.%20Use%20at%20your%20own%20risk.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdd%20this%20to%20the%20workbook%20macro%20section%3A%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Workbook_Open()%0A%0ACall%20RefreshQuery%0A%0AEnd%20Sub%3C%2FPRE%3E%3CP%3EAdd%20this%20to%20a%20module%3A%3C%2FP%3E%3CPRE%3ESub%20RefreshQuery()%20%0A%0AThisWorkbook.Unprotect%20strAdminPassword%0AApplication.EnableEvents%20%3D%20False%0A%0A'This%20value%20check%20looks%20at%20a%20formula%20I%20have%20in%20the%20workbook.%20if%20you%20haven't%20refreshed%20within%20a%20week%20you%20get%20a%201%20%0AIf%20Range(%22SECTOR!B33%22)%20%3D%201%20Then%0A%0A%20%20%20%20StartTime%20%3D%20Time%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20'%20Show%20'Updating..'-message%20Not%20sure%20if%20the%20timer%20is%20needed%20any%20more%2C%20it%20was%20legacy%20from%20something%20else%20but%20it%20works%20with%20it%2C%20so%20hey%20if%20it%20ain't%20broke...%20%0A%20%20%20%20ActiveSheet.Shapes.Range(Array(%22Rectangle%3A%20Rounded%20Corners%201%22)).Visible%20%3D%20True%0A%20%20%20%20nSec%20%3D%200.1%20%2B%20Timer%0A%20%20%20%20While%20nSec%20%26gt%3B%20Timer%0A%20%20%20%20DoEvents%0A%20%20%20%20Wend%0A%20%20%20%20%0A%20%20%20'Change%20%22Connection%22%20to%20the%20connection%20name%20you%20have.%0A%20%20%20%20ActiveWorkbook.Connections(%22Connection%22).Refresh%0A%20%20%20%20%0A%20%20%20%20'%20Remove%20'Updating..'-message%0A%20%20%20%20ActiveSheet.Shapes.Range(Array(%22Rounded%20Rectangle%201%22)).Visible%20%3D%20False%0A%20%20%20%20nSec%20%3D%200.1%20%2B%20Timer%0A%20%20%20%20While%20nSec%20%26gt%3B%20Timer%0A%20%20%20%20DoEvents%0A%20%20%20%20Wend%0A%20%20%20%20%0AEnd%20If%0A%0A'This%20section%20below%20is%20more%20specific%20to%20my%20use%20case%20-%20so%20I%20track%20when%20this%20was%20last%20refreshed%2C%20if%20it's%20older%20than%20a%20week%20I'll%20force%20the%20user%20to%20refresh%20to%20continue%20to%20use%20the%20workbook.%20%0ADim%20cell%20As%20Range%0ADim%20rng%20As%20Range%0ADim%20RangeName%20As%20String%0ADim%20CellName%20As%20String%0A%0A'Single%20Cell%20Reference%20(Workbook%20Scope)%0A%20%20RangeName%20%3D%20%22REFRESH_DATE%22%0A%20%20CellName%20%3D%20%22O1%22%0A%20%20%0A%20%20Set%20cell%20%3D%20Worksheets(%22UCON%20Pricing%20Tool%20(2016)%22).Range(CellName)%0A%20%20ThisWorkbook.Names.Add%20Name%3A%3DRangeName%2C%20RefersTo%3A%3Dcell%0A%0A%0A%0A%0ARange(%22REFRESH_DATE%22).Value%20%3D%20Date%0ARange(%22REFRESH_DATE%22).NumberFormat%20%3D%20%22dd%2Fmm%2Fyyyy%22%0AApplication.EnableEvents%20%3D%20True%0A%0A%0AThisWorkbook.Unprotect%20strAdminPassword%0A%0AEnd%20Sub%3C%2FPRE%3E%3CP%3EYou%20may%20need%20to%20edit%20the%20code%20to%20get%20what%20you%20want%20from%20it%2C%20but%20it's%20a%20base.%26nbsp%3B%20I%20am%20still%20testing%20this%20with%20my%20workbooks%20so%20it%20may%20not%20be%20final.%26nbsp%3B%20Effectively%2C%20use%20at%20your%20own%20risk%2Funderstanding.%20It%20may%20work%20for%20you%2C%20it%20may%20not.%26nbsp%3B%20Currently%2C%20it%20seems%20to%20work%20for%20me%20but%20it's%20a%20pain%20in%20the%20arse%20of%20a%20workaround.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou'll%20need%20to%20disable%20background%20refresh%20for%20your%20connection%2C%20and%20disable%20refresh%20on%20opening.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDavid%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-181726%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-181726%22%20slang%3D%22en-US%22%3E%3CP%3EBe%20aware%2C%20with%20a%20macro%20solution%2C%20that%20you%20may%20have%20to%20remove%20all%20checkboxes%20for%20'refresh%20automatically%20when%20cell%20value%20changes%20'%20for%20all%20queries%20in%20your%20workbook%2C%20and%20to%20refresh%20these%20queries%20with%20a%20command%20well%20from%20your%20macro.%20Similarly%20you%20will%20need%20to%20disable%20'Refresh%20data%20when%20opening%20the%20file'%20and%20possibly%20also%20for%20background%20refresh.%20It%20is%20really%20a%20pain.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-181707%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-181707%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20David%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20share%20your%20macro%3F%20I%20don't%20think%20it'd%20work%20for%20my%20situation%20but%20it%20might%20help%20others%20in%20the%20same%20pickle.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-181702%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-181702%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20this%20same%20issue%2C%20the%20only%20work%20around%20I've%20come%20up%20with%20so%20far%20is%20to%20use%20a%20macro%20to%20run%20when%20the%20workbook%20is%20opened%2C%20unprotect%20the%20workbook%2C%20refresh%20the%20data%20(not%20in%20the%20background)%20and%20then%20lock%20the%20workbook.%26nbsp%3B%20It's%20a%20pain%20in%20the%20arse%20since%20I%20have%20to%20change%20many%20different%20tools.%26nbsp%3B%20Please%20roll%20this%20change%20back!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-181693%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-181693%22%20slang%3D%22en-US%22%3E%3CP%3ESame%20here%2C%20already%20cost%20me%20a%20full%20day%20of%20work%20to%20create%20workarounds.%20The%20workbook%20protection%20is%20an%20essential%20feature.%20Extremely%20poor%20performance%20of%20Microsoft.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180817%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180817%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20having%20the%20same%20problem.%20It%20started%20on%20or%20about%20April%204%2C%202018.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20.xlsm%20file%20which%20has%20a%20data%20connection%20to%20a%20.csv%20sheet%20located%20in%20the%20same%20folder.%20The%20workbook%20is%20protected%20and%20will%20not%20pull%20the%20data%20from%20the%20CSV.%20I'm%20getting%20the%20same%20error%20message%20as%20everyone%20else.%20Removing%20the%20protection%20from%20the%20workbook%20allows%20the%20data%20to%20be%20pulled.%20The%20last%20time%20the%20file%20itself%20was%20modified%20was%20back%20in%20January.%20Hopefully%20this%20is%20a%20bug%20and%20will%20be%20fixed%20soon.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180376%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180376%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20same%20issue%20with%20webquery.%26nbsp%3B%20I%20have%20a%20ton%20of%20clients%20that%20have%20started%20reporting%20issues%20with%20workbooks%20that%20have%20been%20deployed%20to%20them.%26nbsp%3B%20Is%20there%20a%20workaround%2C%20besides%20unprotecting%20the%20workbook%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-179993%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-179993%22%20slang%3D%22en-US%22%3E%3CP%3EMe%20too!%20It%20is%20unbelievable.%20Can%20anybody%20offer%20a%20workaround%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-726507%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-726507%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%26nbsp%3BI%20am%20seeing%20this%20behavior%20on%20Excel%201808.%2010730.20348%20Click-to-run%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20hot%20fix%20or%20other%20patch%20required%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730227%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246313%22%20target%3D%22_blank%22%3E%40MrJoeM%3C%2FA%3E%2C%20can%20you%20please%20share%20a%20screenshot%20of%20your%20Excel%20version%3F%20Go%20to%20File%20%26gt%3B%20Account%20%26gt%3B%20take%20a%20screenshot%20of%20everything%20that%20appears%20under%20Product%20Information%20on%20the%20right%20(remove%20your%20email%20address%20for%20the%20sake%20of%20privacy)%20an%20paste%20it%20in%20your%20reply%20to%20me.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%2C%3C%2FP%3E%0A%3CP%3EGuy.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746657%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746657%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Guy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20encounter%20this%20same%20problem%20and%20I%20have%20Version%201906%20(Build%2011727.20244).%20Initially%20I%20wanted%20to%20extract%20data%20from%202%20protected%20workbooks%20from%20Onedrive%2C%20they%20worked%20just%20fine%20when%20unprotected%20but%20when%20protected%20I%20got%20a%20message%20that%20data%20was%20corrupted.%20I%20thought%20a%20workaround%20would%20be%20keeping%20a%20copy%20offline%20of%20both%20workbooks%20and%20use%20power%20query%20locally%20but%20with%20no%20success%2C%20I%20got%20the%20same%20error%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20too%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3ERodrigo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-750737%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20664px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123058i53402A2156FEBF71%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20Version.PNG%22%20title%3D%22Excel%20Version.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753918%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753918%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246313%22%20target%3D%22_blank%22%3E%40MrJoeM%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20unable%20to%20reproduce%20the%20problem%20locally.%20Can%20you%20share%20the%20problematic%20workbook%20with%20me%20please%20and%20a%20video%20recording%20of%20the%20scenario%3F%20Make%20sure%20there%20is%20no%20sensitive%20or%20private%20data%20in%20your%20workbook%20though.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy%3C%2FP%3E%0A%3CP%3E-%20Excel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753921%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753921%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373845%22%20target%3D%22_blank%22%3E%40rodrocdl%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20your%20description%20I%20suspect%20it%20may%20be%20a%20different%20issue.%20Can%20you%20please%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EShare%20your%20Excel%20version%20with%20me%3C%2FLI%3E%0A%3CLI%3EShare%20step-by-step%20instructions%20with%20me%20so%20I'll%20be%20able%20to%20reproduce%20the%20problem%20locally%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EGuy%3C%2FP%3E%0A%3CP%3E-%20Excel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755296%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755296%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%20Here%20is%20what%20you%20asked%20me%20to%20provide%20to%20you%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Version%201906%20(Build%2011727.20244%20Click-to-Run)%2C%20Also%20I%20attached%20a%20screenshot%20my%20Product%20Information%20section.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%26nbsp%3BI%20have%20protected%20workbooks%20in%20a%20folder%2C%20I%20need%20to%20load%20their%20data%20into%20my%20Data%20Model.%20I%20use%20a%20main%20Excel%20workbook%20where%20I%20want%20to%20run%20Power%20Query%20to%20load%20all%20that%20data%20into%20one%20place.%3C%2FP%3E%3CP%3ESo%20the%20steps%20are%20as%20follow%3A%3C%2FP%3E%3CP%3E-%20I%20click%20on%20Data%20Ribbon%20%26gt%3B%20Get%20%26amp%3B%20Transform%20Data%20%26gt%3B%20Get%20Data%20%26gt%3B%20From%20File%20%26gt%3B%20From%20Workbook.%3C%2FP%3E%3CP%3E-%20Then%20a%20window%20appears%20%22Import%20Data%22%20and%20I%20search%20for%20the%20folder%20where%20protected%20workbooks%20are.%3C%2FP%3E%3CP%3E-%20I%20select%20a%20single%20workbook%20and%20click%20the%20button%20%22Import%22.%3C%2FP%3E%3CP%3E-%20Then%20I%20get%20this%20error%20message%20%22File%20contains%20corrupted%20data%22.%20(Attached%20a%20screenshot%20of%20error%20window)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELoading%20data%20into%20Power%20Query%20works%20just%20fine%20when%20the%20workbook%20is%20unprotected%20but%20I%20can't%20make%20it%20work%20when%20protected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%2C%3C%2FP%3E%3CP%3ERodrigo%20Chin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758559%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758559%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373845%22%20target%3D%22_blank%22%3E%40rodrocdl%3C%2FA%3E%26nbsp%3B%2C%20define%20%22protected%20workbook%22%20please.%20How%20do%20you%20protect%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759396%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759396%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%2C%20Protected%20workbooks%20as%20%22Encrypt%20with%20password%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%20tab%20%26gt%3B%20Info%20%26gt%3B%20Protect%20Workbook%20%26gt%3B%20Encrypt%20with%20password%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-762733%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20no%20longer%20refresh%20data%20in%20a%20Protected%20Workbook%20since%20Excel%20version%201803%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-762733%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373845%22%20target%3D%22_blank%22%3E%40rodrocdl%3C%2FA%3E%2C%20this%20is%20a%20know%20gap.%20Get%20%26amp%3B%20Transform%20(Power%20Query)%20cannot%20import%20data%20from%20a%20password%20protected%20Excel%20workbooks.%3C%2FP%3E%0A%3CP%3EI%20apologize%20for%20inconvenience%20but%20the%20only%20workaround%20is%20to%20remove%20password%20protection%20from%20the%20source%20workbook%20before%20importing%20the%20data%20from%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy%3C%2FP%3E%0A%3CP%3E-%20Excel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E
Alex Lush
Occasional Contributor

Seems to have been a functionality change introduced in version 1803.

 

We have a protected workbook with some data connections to pull in data from SQL. Since updating to version 1803 the message 'Workbook is protected and cannot be changed' appears when trying to refresh the data. Removing workbook protection allows data to be refreshed.

 

Using the same workbook in version 1802 allows the data to be refreshed with the workbook protection left on.

 

Posting here in case anyone else experiences this issue.

50 Replies

Me too! It is unbelievable. Can anybody offer a workaround?

Yes same issue with webquery.  I have a ton of clients that have started reporting issues with workbooks that have been deployed to them.  Is there a workaround, besides unprotecting the workbook?

 

Help!

I'm having the same problem. It started on or about April 4, 2018. 

 

I have a .xlsm file which has a data connection to a .csv sheet located in the same folder. The workbook is protected and will not pull the data from the CSV. I'm getting the same error message as everyone else. Removing the protection from the workbook allows the data to be pulled. The last time the file itself was modified was back in January. Hopefully this is a bug and will be fixed soon.

Same here, already cost me a full day of work to create workarounds. The workbook protection is an essential feature. Extremely poor performance of Microsoft.

I have this same issue, the only work around I've come up with so far is to use a macro to run when the workbook is opened, unprotect the workbook, refresh the data (not in the background) and then lock the workbook.  It's a pain in the arse since I have to change many different tools.  Please roll this change back! 

Hey David,

 

Can you share your macro? I don't think it'd work for my situation but it might help others in the same pickle.

Be aware, with a macro solution, that you may have to remove all checkboxes for 'refresh automatically when cell value changes ' for all queries in your workbook, and to refresh these queries with a command well from your macro. Similarly you will need to disable 'Refresh data when opening the file' and possibly also for background refresh. It is really a pain.

Hi Brian,


Sure, happy to. I'm no Excel expert but put this together this afternoon. Use at your own risk. 

 

Add this to the workbook macro section:

Private Sub Workbook_Open()

Call RefreshQuery

End Sub

Add this to a module:

Sub RefreshQuery() 

ThisWorkbook.Unprotect strAdminPassword
Application.EnableEvents = False

'This value check looks at a formula I have in the workbook. if you haven't refreshed within a week you get a 1 
If Range("SECTOR!B33") = 1 Then

    StartTime = Time
                
    ' Show 'Updating..'-message Not sure if the timer is needed any more, it was legacy from something else but it works with it, so hey if it ain't broke... 
    ActiveSheet.Shapes.Range(Array("Rectangle: Rounded Corners 1")).Visible = True
    nSec = 0.1 + Timer
    While nSec > Timer
    DoEvents
    Wend
    
   'Change "Connection" to the connection name you have.
    ActiveWorkbook.Connections("Connection").Refresh
    
    ' Remove 'Updating..'-message
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Visible = False
    nSec = 0.1 + Timer
    While nSec > Timer
    DoEvents
    Wend
    
End If

'This section below is more specific to my use case - so I track when this was last refreshed, if it's older than a week I'll force the user to refresh to continue to use the workbook. 
Dim cell As Range
Dim rng As Range
Dim RangeName As String
Dim CellName As String

'Single Cell Reference (Workbook Scope)
  RangeName = "REFRESH_DATE"
  CellName = "O1"
  
  Set cell = Worksheets("UCON Pricing Tool (2016)").Range(CellName)
  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell




Range("REFRESH_DATE").Value = Date
Range("REFRESH_DATE").NumberFormat = "dd/mm/yyyy"
Application.EnableEvents = True


ThisWorkbook.Unprotect strAdminPassword

End Sub

You may need to edit the code to get what you want from it, but it's a base.  I am still testing this with my workbooks so it may not be final.  Effectively, use at your own risk/understanding. It may work for you, it may not.  Currently, it seems to work for me but it's a pain in the arse of a workaround. 

 

You'll need to disable background refresh for your connection, and disable refresh on opening. 


David

Yes this is a big problem. 

 

My query is refreshed using VBA.  I use a class object for the query so it can run in the background and trigger events.  My workaround is to use the BeforeRefresh event to unlock the workbook, and the AfterRefresh event to lock it again.  I have not tried to see if these events are triggered when a user selects Refresh All from the data ribbon. 

 

Of course I do have to update all the workbooks that I have deployed!

 

Hi David,

 

Correct me if I am wrong, but shouldn't the last line in your RefreshQuery sub actually be:

 

ThisWorkbook.Protect strAdminPassword
Yes, it should! I realised my mistake when I deployed it to a couple of our tools today.

It isn't as elegant solution as I'd like, preferably I wouldn't want to have to implement a solution, but it does the job for the moment.

David

All,

I think if we want Microsoft to move and take some action to correct this, we need a lot more complaints in this thread than those of the few poor souls that are here now, who actually understand what is wrong. Suggest to mobilize your customers / colleagues to join in here.

I logged a call with Office 365 a few days ago to determine if this was a bug or an intended functionality change. Watch this space. I cited this thread in the ticket hopefully they are seeing people's frustration!! I will update this thread as I hear more.

Thank you David very much for the macro, it works again! 

 

I encountered the same problem, my excel needs to download exchange rates and needs to be protected. Since update I can no longer have the workbook protected and refresh connections.

 

When using macro to refresh connections, it is necessary to disable refresh of connection on the background and to disable refresh of connection when opening the workbook, otherwise macro locks the workbook before connection is updated / shows message, that the workbook is protected and cannot be changed.

 

Anyway I would appreciate solution from Microsoft and if possible - without necessity to use macros at all. The last update is a real pain in the arse.

Glad I could help! It's not a perfect solution but at least it gives us the ability to still use workbooks in the same way for the moment. I've reached out to Microsoft in a couple of other ways as well to see if we can find out something more. 

Here is some code that I adapted for other purposes a while back. I have added ThisWorkbook.Unprotect and ThisWorkbook.Protect to it to get around the bug.  These are in events that are triggered before and after a refresh so you can have a background refresh and the workbook will only be protected again once the AfterRefresh event is triggered.  Use it at your own risk, but it is serving me well.

 

In ThisWorkbook add the following declaration  

Dim QueryAnswer As clsQueryDone

Then add the following to the Workbook_Open event.  Make sure to change the sheet name and query number to the correct ones. 

Private Sub Workbook_Open()
    'Associate QueryAnswer with QueryTable 
    'Create query class object
    Set QueryAnswer = New clsQueryDone
    QueryAnswer.HookUpQueryTable Sheets("Sheet1").QueryTables(1)

 Set up a Class Module called clsQueryDone and add the following to it:

Private WithEvents MyQueryTable As QueryTable
Private Sub MyQueryTable_AfterRefresh(ByVal Success As Boolean)
ThisWorkbook.Protect strMasterPwd
If Success Then
'Do whatever you want
Else
MsgBox "There has been an error with the Query Refresh", _
vbOKOnly Or vbCritical, "Refresh Error"
End If
End Sub 
Private Sub MyQueryTable_BeforeRefresh(Cancel As Boolean)
ThisWorkbook.Unprotect strMasterPwd
'and do whatever you want
End Sub 
Friend Sub HookUpQueryTable(qt As QueryTable)
Set MyQueryTable = qt
End Sub 

 Andrew

This is a major problem. Everybody vote this thread up and send complaint to Microsoft helpdesk.

Coded workbooks are usually used by a lot of people, and then suddenly production halts. Not fun.

 

 

A problem with the solution to open/close workbook:

If the code have to unprotect/protect for every data read, this takes time to run (since it decrypt/encrypt the workbook), making for a lesser user experience.

If this is intentional by Microsoft, at least there should be an option to toggle the new functionality of  "protect workbook" on/off.

So I just checked the ticket I had logged on this with O365 support and it appears to have been closed without any response. Thanks Microsoft!

 

If anyone from MS Support is looking at this, the ticket number is #7854947, please reopen and provide a response. Thanks.

That's frustrating, I've reached out on Twitter again to the social team to see if they can chivvy something along, would suggest to do the same and raise more tickets about the same issue.  They may not do anything unless there's enough noise. 

This has created MAJOR issues for me. I have loads of protected models and reports that extract info via SQL that are business critical. Please microsoft, can you look at this this problem ASAP?

I'm trying to get a ticket raised on O365 support, but as I'm not the company administrator it's proving irritating.  

I spoke to a microsoft representative for 2 hours, that was asigned via our administrator, no resolution at all...then just got pointed to another support group on the internet that i can not join...is getting rather frustrating!

Hi,

 

It is a known regression and we are working on a fix. Stay tuned and follow up the updates on Tech Community.

 

Guy
- Excel Team

Thank you Guy! :) Please keep us updated.  

 

David


@Guy Hunkin wrote:

Hi,

 

It is a known regression and we are working in a fix. Stay tuned and follow up the updates on Tech Community.

 

Guy
- Excel Team


 

@Guy Hunkin- many thanks for your update.

I did finally receive an update to the ticket I logged through O365, although the information directly contradicts what Guy has said in this thread!

 

[Ticket #:7973974]

Hello Alex,

This issue is by design and even Out of Support for Office 365 Technical Support.
You may share your valuable feedback for this on the below support links - 

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/32287204... 
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/34126504... 

Should you have any additional queries, please let me know.

Thanks
Ankit Shukla
Microsoft Office 365

CwsRequestId:7438ad61-ee11-4d5f-a9de-af58cf7fa14a

I have the exact same problem.

I hope a solution will be found soon. 

Same problem here.

 

If it's a feature and not a bug (which I'm really hoping is not the case) then I'd like an option to not use it. I honestly can't see what benefit would there be to having it work like that.

Above, @Guy Hunkin suggested that it isn't a feature and the team is working on a fix.   Guy - do you have any update? 


Thanks,

David

The problem seems to be solved now. I don't get the pop-up anymore when I open the workbook. 

Is it the same for you? 

 

Kind regards

Nicolaj

It seems to be working now (Version 16.0.9226.2156). Thank you for letting us know.
Solution

Hi there,

 

The fix was deployed for the following Office versions:

  1. Version 1803 (Build 9126.2196) and later
  2. Version 1804 (Build 9226.2135) and later
  3. Version 1805 (Build 9330.2017) and later

Hope this helps.

 

Guy.

- Excel Team

Hi Guy,

Thanks for the fix, however it now appears something else has changed to do with queries. In our documents we're using we have an Excel file connection that pulls some data in that is centrally updated, in versions up to the latest this seemed to work fine. Now it appears the latest version of Excel won't allow us to refresh that connection and asks for a Microsoft Access Database Engine OLE DB Initialization Information. 

 

I tried replacing the query using the latest version of Excel, and it works fine, until you try opening the document with an older version of Excel and I get this error: image.png

 

 

 

 

Any ideas for a fix? I seem to have one version that works on older versions (e.g. 2010) and one that works with the new version of Excel 365 v1805 build 9330.2087.

 

Thanks,

David

 

 

 

Hi David,

 

I am not sure I understand your scenario. Are you saying that you have a query that cannot be refreshed with an older Excel version anymore?

 

Can you please provide step-by-step instructions for us to repro the problem locally? I will also appreciate if you can share a problematic workbook with me? Just make sure to remove or scramble all the sensitive or private data before sharing your workbook.

 

Guy.

Hi,

 

Easy fix. Just protect sheet and workbook with new password of ur choice and then unprotect again...should be all sorted.

 

Regards

Hi Guy,

 

I am still running into the same problem. I am running Excel version 1803, build 9126.2295 (see below).

image.png

 I am running a basic query between two Excel workbooks on the same network. When the sheet I want to update is protected (default protection settings), I receive the below error.image.png

 

Edit: to clarify that I receive the error message when selecting "Refresh All", in case that wasn't clear.

 

Any advice?

 

Thanks,

 

Mike

 

 

@Michael Stephenson, I am checking this. In a meantime please un-protect the sheet as a workaround.

Hi @Michael Stephenson,

 

Can you please upgrade your Office version? This should be resolved in the latest semi-annual Office build version 1808 (build 10730.xxxxx).

 

Guy

- Excel Team

Hi Guy,

 

No, unfortunately my Office updates are managed by the system admin and I cannot update Office until they schedule updates for the entire group. I do not know when these will be scheduled.

 

-Mike S

@Michael Stephenson, I'd like to suggest that you speak with your IT on this topic then. Re-install should probably solve the issue for you.

 

Guy

- Excel Team

@Guy Hunkin I am seeing this behavior on Excel 1808. 10730.20348 Click-to-run

 

Is there a hot fix or other patch required?

@MrJoeM, can you please share a screenshot of your Excel version? Go to File > Account > take a screenshot of everything that appears under Product Information on the right (remove your email address for the sake of privacy) an paste it in your reply to me.

 

Thanks,

Guy.

@Guy Hunkin 

 

Hi Guy,

 

I encounter this same problem and I have Version 1906 (Build 11727.20244). Initially I wanted to extract data from 2 protected workbooks from Onedrive, they worked just fine when unprotected but when protected I got a message that data was corrupted. I thought a workaround would be keeping a copy offline of both workbooks and use power query locally but with no success, I got the same error message.

 

Can you help me too?

 

Thank you,

Rodrigo

Hi @MrJoeM ,

 

I am unable to reproduce the problem locally. Can you share the problematic workbook with me please and a video recording of the scenario? Make sure there is no sensitive or private data in your workbook though.

 

Guy

- Excel Team

Hi @rodrocdl ,

 

From your description I suspect it may be a different issue. Can you please:

  1. Share your Excel version with me
  2. Share step-by-step instructions with me so I'll be able to reproduce the problem locally

Guy

- Excel Team

Hi @Guy Hunkin 

 

Thank you for your help. Here is what you asked me to provide to you:

 

1. Version 1906 (Build 11727.20244 Click-to-Run), Also I attached a screenshot my Product Information section.

 

2. I have protected workbooks in a folder, I need to load their data into my Data Model. I use a main Excel workbook where I want to run Power Query to load all that data into one place.

So the steps are as follow:

- I click on Data Ribbon > Get & Transform Data > Get Data > From File > From Workbook.

- Then a window appears "Import Data" and I search for the folder where protected workbooks are.

- I select a single workbook and click the button "Import".

- Then I get this error message "File contains corrupted data". (Attached a screenshot of error window)

 

Loading data into Power Query works just fine when the workbook is unprotected but I can't make it work when protected.

 

I appreciate your help.

 

Best,

Rodrigo Chin

@rodrocdl , define "protected workbook" please. How do you protect it?

@Guy Hunkin, Protected workbooks as "Encrypt with password"

 

File tab > Info > Protect Workbook > Encrypt with password