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

I also have this issue in Excel.  Version is Excel for Office 365 MSO (16.0.12527.21296) 64-bit.  If you lock a worksheet and have a query table on it, the query cannot refresh.  You also cannot allow that option as it isn't in the list of checkboxes when enabling Protect Worksheet.

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

irisw ,

Please provide more details about your Excel version and your exact scenario.

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-enable-data-query-refresh-with-powerpivot-tables 
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/34126504-sql-queries-won-t-refresh-on-protected-workbook-si 

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

Thanks
Ankit Shukla
Microsoft Office 365

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

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