Password request for connecting to a protected xls file

[whoiswct]

I create a Pivot Table and use external connection to connect the data.
The data is a shared excel file (office 2010) with password protected. I have the password.

When I connect the PT to the protected data, it pop up for a password request. I input the correct password but cannot connect.
Something doing wrong?

Pls kindly advise. Thanks.

[Ken Puls]

What is the exact error message that you get when it can't connect?

[whoiswct]

I attached the data file (test_pwd.xlsx) and the open password is 123.

Pls try to use another excel file to create pivot table and connect by external connection. Just like the screendump.

I don't know what should I do even though I know the open password.

Thanks.

[Ken Puls]

This is kind of interesting...

If I open the test workbook first, then I'm able to update the pivottable. If I don't though, then I get stuck in error messages trying to provide the correct username and password.

Without knowing the password setup to get this right, I'm wondering about coding a routine to open a copy of the target workbook first, before the update happens. Should be able to do that via VBA. Does that sound of interest?

[whoiswct]

If I open the test workbook first, I also cannot connect the data. How can you do that?

Sent from my phone using Tapatalk

[Ken Puls]

I opened the workbook, and provided the password manually. It just worked when I did that.

[whoiswct]

I opened the workbook, and provided the password manually. It just worked when I did that.

If the data source is a "share workbook", it still cannot be connect even though provoide the password mannually.
Any work-a-round solution such as VB code?