Forum Discussion
Can no longer refresh data in a Protected Workbook since Excel version 1803
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
I am using version 1909.
Have a worksheet with query table linked to a table on another worksheet in the same book.
When I protect the query table worksheet and Refresh the query, I get the error:
Can you confirm if this is expected? Sorry if I missed it above but I thought this was fixed in a previous patch.
Many thanks,
K
MicrosoftKR_2019 ,
I am not sure I understand your scenario. What do you mean by "query table linked to a table on another worksheet"? Can you provide a sample workbook that demonstrates this scenario? Just make sure it doesn't contain any sensitive or private information before sharing.
Guy
- Excel Team
- Guy Hunkin
Liz_wacoal , I am not aware of such a workaround. However, I suggest you publish your question at the General Discussion forum. Maybe someone will have a creative solution for you.
Guy
- Excel Team
Guy Hunkin Is there any way to override this? I want to protect most of a sheet that has a query in it, but allow users to edit certain cells.
- Guy Hunkin
JBreckeen, refresh of Power Query queries works if you protect the workbook structure. It will not work if you protect the worksheet. Hope it helps.
Guy
- Excel Team
- Guy Hunkin
KR_2019 , the behavior that you described is by design. You cannot refresh Power Query query is loaded into a protected sheet. However, if you protect the workbook structure then the refresh should work for you.
Guy.
- Excel Tteam
I found this page describing the issue I am still having.
I have several Tables of Data on the Blue worksheets. There are tables on the Red sheets utilizing Queries from the tables on the Blue sheets. The Blue sheets are protected with no issues.
But the attached .png shows the issue I am having when I protect the Red sheets. I have tried enabling different amounts of protection, but as soon as I put any protection on the Red sheets I get the error. This happens when there is not even a change in the source data.
It seems the same issue that others were having above, but my version is newer than the fixed build. Did this issue reappear? Or is this something new?
Thanks in advance,
Hi Guy Hunkin
Thanks for getting back to me.
I mean that within the same workbook, I have one sheet with a Source table, and another sheet with a table that is the result of a 'From Table' Data query pointed at the Source table. I want to protect the Query table in the second sheet, but by protecting the sheet with a password, I am unable to Refresh the query.
See this sample, the password for the protected Query tab is 'password'.
Many thanks,
K
