Hi Guys!
Thank you for this wonderful site--this site has been super helpful for many code problems I've run into in the past.
I'm trying to extract data from a password-protected workbook that also has links to data from a second password-protected workbook. I know the passwords for both workbooks, and both passwords are the same. I see two ways to perhaps do this, but I can't figure out how to enter a password for either way...
The first way is to do something like this:
Workbooks.Open Filename:=C:\PATH\ProtectedWorkbook.xlsm, Password:="Password"
Then, I'd want to extract data directly from the opened workbook. However, after the ProtectedWorkbook.xlsm document opens with the correct password entered, it prompts for the second password to the linked password-protected data. I've tried to open the second document first and input the password, but when the main document (Protectedworkbook.xlsm) is opened afterwards, it still asks for the second workbook's password. Is there a way to input both passwords when Protectedworkbook.xlsm is opened?
The other way I've Googled about is to do something like this:
This uses the looked up CELL value (e.g. "A2" or "B7") from the "Lookup" sheet and uses it to find the corresponding cell value within ProtectedWorkbook.xlsm's Sheet1. Then it places the looked-up value into the "Value" sheet. The Lookup sheet and Value sheet are in a different workbook than ProtectedWorkbook.xlsm. This also asks for the password of the ProtectedWorkbook.xlsm file (but not the second workbook's password). I'm not sure how to automate its entry as I have a few hundred values to grab.... Is there a simple way to add the password into this code?
I've heard of Indirect.EXE, but I think it might be best if I could use a VBA code to open the workbook and extract or grab values somehow..
Any help would be greatly appreciated!
Thanks!
Bookmarks