VBA - Fill password of multiple linked PW protected workbooks

Hi all,

First post here, hopefully the contents are sufficient for you experts to assist

Scenario:

1. I have a "master.xlsm" workbook that pulls data from multiple other workbooks which are encrypted with a password.
2. The workbooks can be called "01WB.xlsm", "02WB.xlsm", "03WB.xlsm"
3. The passwords can be "PW1", "PW2", "PW3"

Problem:
This causes multiple password prompts in the master.xlsm workbook whenever updating.

Solution sought:
VBA module that fills out the password for each protected workbook linked to the master.xlsm

I apologize if I left out any necessary information, I tried my best to make it as clear as possible.

Thank you in advance!

Show the code you use to retrieve data from the source workbooks.

Artik

Hi Artik

I wish there was a code! It's a very basic setup - the master workbook simply points directly to each cell in the encrypted workbooks from where it wants the information.
There are for sure very nice ways this could have been done, but instead it was done without coding

Do the prompts for passwords already appear when the master file is opened, or only later when the data in it is changed?

Artik

Yes.
The prompts happen when I first open the file and it seeks to connect, to my knowledge there is no re-prompt as long as I don't add new reference cells to the encrypted workbooks. There are about 15 external encrypted workbooks, so every time I open the file, it asks me for 15 passwords.

Is the following correctly understood?

- I create a separate file, which has a code (containing the passwords for the encrypted files) to open all the encrypted files, after which as a last step it opens the master file?

Security is not a big issue in this case, its mostly to keep certain people out of certain files - they're not malicious people, just unintentional wrecking balls so to speak :D

Yes, you understood my intentions correctly.

Artik

Would you happen to be able to produce such a code maybe just with like 2-3 placeholder workbooks so I can add in the rest myself. I am unfortunately not well versed within the VBA world or any programming world for that matter

Do the source files have a fixed location?
If the answer is Yes, can the files be in the same folder as the Master file?
Since my plan is to close the source files as soon as I open the Master file, the question arises as to whether new references to the source files are created manually while working with the Master file. If Yes, then again there will be a problem, because after entering the formula there will be an attempt to connect to the source file, which will result in a prompt for the file password. In that case, you would need to have the source files open all the time (I think they may be hidden). However, until the Master file is closed, working in the source files may not be possible.

Artik

Currently the master file and source files are in the same folder, and I was intending on keeping it like that unless otherwise necessary. The location is fixed.
However, some people may be inserting data into the source files at completely random times, would that be an issue?

Originally Posted by slimjim84

However, some people may be inserting data into the source files at completely random times, would that be an issue?

Yes, this will be a problem because there is a source file open on one computer. Therefore, another user (on another computer) can open the file, but it will be in Read Only mode. In such a state, the other user will have a problem writing the newly entered data.

Artik

Ohh okay I see. Bugger, and there would not be a code I could do in the master file, which just enters the passwords from the 15 source files?

The code can itself be in the Master file but it will not work as expected. When a file containing macros is opened, the formulas are recalculated first and only then are the macros run. Such an order does not allow the code to be used in the master file.
Something else came to mind as I was thinking about it, but I need to check it first. It will take a little longer, so come tomorrow.

Artik

Ahh I see!
Thank you for helping me understand! No stress! And thank you for looking into this!

Hi Artik

Hope you're well!
Did you have time to look into this a bit further?
Have a lovely weekend!

I tried two ways.
The first. Open the source files in read-only mode, and finally open the master file. It takes quite a long time to open 15 files, especially when they contain a lot of data. Since copies of the files are opened, changes to the source files made by other users (on other computers) do not update in the Master file.
The second. Source files were saved as shared (in an older version of sharing). Opening the source files and finally opening the Master file. Basically the same problems occur as in method one. When opening the Master file, the data is up to date, but after changing the data in the source files, it does not update in the Master file.
I'm afraid I'm not able to help. In general, downloading data from external sources, especially distributed data, is not a good idea. There are other methods of storing source data, but you would have to completely change the philosophy of operation.

Artik

Thank you for all your time and support in this Artik.
I sent you a PM, hope that is alright