Updating Links from Password Protected Sources

I created 2 Password Protected Excel Workbooks and 1 Master Excel Workbook. The Master Excel Workbook are linked to the 2 Password Protected Excel Workbooks to get the data from them. Whenever I open the Master Excel Workbook, there will be a prompt asking do I want to update or not and I click "Update Link," but another prompt will follow asking for a password. The password prompt will reoccur every time I open the Master Excel Workbook. I was hoping to find a way to have the Master Excel Workbook contain the two passwords, so the next time I open the Master Excel Workbook, I won't have to type in the password.

I created a new module in Visual Basic and have tried this code but it doesn't seem to work:

Formula:

Please Login or Register  to view this content.

Thank you very much for any help provided. I have been googling and trying to figure out different method but the password prompt still shows up.

Hi kev_°

Sorry, I might not have explained it clearly. The two password protected excel files are encrypted with a password, protecting the entire workbook. The sheets are protected but I have certain cells that users are allowed to add values in. So, I want to write a VBA code that when you open the Master Excel Workbook, it will still prompt to ask the user if he/she wants to update but automatically insert password to password prompt.

Also, I tried the open only code, but I still get a password prompt.

Thank you so much for answering this post.

Hi kev_°,

Thank you for the quick response and thank you so much for your codes! I used the code in post #5 and after some tweaking it worked. There was something strange about this. When I copy and paste the code from post #5 into ThisWorkBook module and saved it and reopen the workbook; I still get an Excel prompt asking if I want to update it or not and when I click don't update, then the vba update would appear. So, I used step 1 from Post #4 to turn it off but for some reason it was already ticked on "Don't display the alert and don't update automatic links." I clicked it again and saved the workbook. Once I open again, it was working smoothly. I remember having the option to allow the user to choose whether to update or not, so I assume that this part of the code in post #5 is not working:

Please Login or Register  to view this content.

I am curious why this didn't work but thank you again for your codes.

Thank you again for your help!

Hi kev_°,

Thanks for everything. I have a question about the update process. When I click yes for the vba update prompt, it will briefly open the document and close the document, and will repeat this for the number of files I have added in the vba code you provided. Is it possible to skip the part where it shows the document is opening and closing?

Thank you so much kev_°!! Everything is working beautifully.

Hello, can you please tell me how you tweaked this so I can use the code, its not working for me it keeps prompting me to select UpdateLinks

Hi, thank you so much for these codes. But I have question.
These 2 protected worksheets are currently open to another users, when I open the Master Excel it requires again to enter the passwords of these worksheets. Is there anyway that I can update the Master file without entering their password?