Apr 04 2018 04:08 AM
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.
Apr 05 2018 07:53 PM
Me too! It is unbelievable. Can anybody offer a workaround?
Apr 07 2018 09:27 AM
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!
Apr 09 2018 10:04 AM
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.
Apr 11 2018 07:02 AM
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.
Apr 11 2018 07:32 AM
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!
Apr 11 2018 07:39 AM
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.
Apr 11 2018 08:22 AM
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.
Apr 11 2018 08:49 AM - edited Apr 11 2018 08:52 AM
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
Apr 13 2018 08:23 AM - edited Apr 13 2018 08:50 AM
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!
Apr 13 2018 08:46 AM
Hi David,
Correct me if I am wrong, but shouldn't the last line in your RefreshQuery sub actually be:
ThisWorkbook.Protect strAdminPassword
Apr 13 2018 10:12 AM
Apr 13 2018 12:54 PM
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.
Apr 13 2018 01:46 PM
Apr 16 2018 12:19 AM - edited Apr 16 2018 12:19 AM
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.
Apr 16 2018 01:16 AM
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.
Apr 19 2018 03:08 PM - edited Apr 19 2018 03:12 PM
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
Apr 26 2018 12:36 AM
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.
Apr 26 2018 12:49 AM
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.
Apr 26 2018 01:09 AM
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.
Jun 04 2018 08:26 AM
SolutionHi there,
The fix was deployed for the following Office versions:
Hope this helps.
Guy.
- Excel Team