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

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.

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

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

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,

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

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

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

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

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

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

Guy Hunkin, Protected workbooks as "Encrypt with password"

File tab > Info > Protect Workbook > Encrypt with password

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

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

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

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

Guy Hunkin 

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