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

[Alex Lush]

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.

[#2]

@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

[#3]

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

Is there a hot fix or other patch required?

[#4]

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

[#5]

@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

[#6]

@Guy Hunkin 

[#7]

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

[#8]

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

[#9]

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

[#10]

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

[#11]

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

File tab > Info > Protect Workbook > Encrypt with password

[#12]

@rodrocdl, this is a know gap. Get & Transform (Power Query) cannot import data from a password protected Excel workbooks.

I apologize for inconvenience but the only workaround is to remove password protection from the source workbook before importing the data from it.

Guy

- Excel Team

[#13]

@Guy Hunkin 

I am having the same issue as others.  I am using Version 1808 (Build 10730.20280).

I am receiving this message when I try to refresh SQL data on a protected worksheet.

Please help!

Kind Regards,

John

[#14]

@johnsnow ,

Can you please go to File > Account > take the screenshot of everything under the Product Information pane on the right and paste it here?

Guy

- Excel Team

[#15]

@Guy Hunkin 

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

[#16]

@KR_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

[#17]

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

[#18]

@Guy Hunkin 

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,

[#19]

@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

[#20]

@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

[#21]

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