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

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

excel

office 365

Guy Hunkin
Jun 04, 2018
Hi there,

The fix was deployed for the following Office versions:

Version 1803 (Build 9126.2196) and later

Version 1804 (Build 9226.2135) and later

Version 1805 (Build 9330.2017) and later

Hope this helps.

Guy.

- Excel Team

Petr Potociar

Copper Contributor

Apr 16, 2018

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.

Andrew Nevard

Copper Contributor

Apr 19, 2018

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

Forum Discussion

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