Macro to update link and input password for protected file (without opening it)

Hello everyone, first-time poster and VBA novice looking forward to your input. I will try to follow the forum rules but apologies if I miss anything.

I have a file that is linked to a password-protected workbook. The links are setup so that they work without the linked file being open.

I am attempting to setup a macro that when activated will initiate an Update Values command and enter the subsequent password when prompted (note, this password will remain static and can be hard-coded).

I found the following code, which was initially successful:

Please Login or Register  to view this content.

My issue is that after adding addition sheets to this file, it now repeatedly queries for a password when run. If I manually open edit links and update values, I only get one password prompt and it runs far quicker.

I attempted to record my actions to understand another route, but only gleamed

Please Login or Register  to view this content.

, with the password entering portion seeming to not be recorded - I'm guessing as a security measure. I've attempted variations of send keys, but have been unable to make an extended version based off of this that works.

If anyone could help explain to me what is causing my initial code to now repeatedly request passwords and how to fix it - or how to make a new macro off of this 2nd code snippet (or something better) it would be greatly appreciated.

PS, the macro itself if also password protected to prevent user from viewing password