Need a little help please. Is there a way to automatically update links without having to enter the password for each workbook. I have a workbook that collects data from a number of workbooks (sources). The sources are password protected. Every time I open the workbook that collects the data it wants the password for each source workbook. Is there a way to get around this process?

I'm currently trying this code but, it's not working. Can anyone see anything wrong with it?

Sub Open_Protected_File()
  Workbooks.Open Filename:=D.Shane_Prod_Log.xlsx
    C: Department Records - admin \ XXX_XX_XX_XXX \ D.Shane_Prod_Log.xlsx, Password:="Dperic#7"
End Sub 
  • 1
    I don't think so, otherwise anybody could steal data from your workbooks. You may write a macro, store the passwords there, and it could open the workbooks, however that's also a security risk. – Máté Juhász Mar 17 '17 at 11:56
  • I updated my question. I'm using a macro on the destination file to open the sources but, it's not working. The destination file is also going to be password protected eventually. Thank you – j.vaz Mar 17 '17 at 12:47
  • Not too worry about security. The destination workbook is only avail to Managers. Just don't want to have to enter the password for all the workbooks in order to update links. There's over 100 workbooks (sources) – j.vaz Mar 17 '17 at 13:43
  • Have you tried just entering the link as a relative path? ='Boox_Y.xlsx'!A1 for example? – ejbytes Mar 18 '17 at 8:52
  • I have not tried anything else. Just pretty much finding variations of this code. The Summary Book is on a shared drive along with the sources but the code does not work. The summary book continues to ask for each password, for each workbook (source). – j.vaz Mar 20 '17 at 14:08

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.