cwunderlich
Board Regular
- Joined
- Sep 24, 2010
- Messages
- 101
Hello,
I currently have macro which does the following:
Reads from a master list of files to determine which individual files the user wants to open and then check certain things in each file. Each of these files are password protected (most with the same password). I have code which allows the user to enter a password into a MsgBox at the beginning of the macro (call it MyPassword). The macro then uses this password as it goes forward and opens each of the files (so the user doesn't keep getting password popups each time a new file is opened).
However, some of these indivdual files have many links to other password protected workbooks within them. So, for a time, here is the work around I coded (remember the intent here was that the user would not have to enter in any prompts after the initial kickoff, aka they wouldn't have to sit there and babysit the macro, they could kick it off for many files on one machine and walk away):
- open up the individual file (call it FileA) using the MyPassword variable. Then determine all the links within this file. I could not figure out a way to have the VBA code automatically enter in MyPassword into the UpdateLinks prompt that the user gets when opening a file with links. So my workaround was to open up each of the link files (using the MyPassword variable) and activating each of these so the links in FileA would get updated and then closing all the link files. Then I could move forward with my checks.
However, some of these files have become filled with so many links it takes a very long time to open up all the link files. When link files have their own links, it just goes on and on and on. So for these type of files, I have the macro determine if it is one of these files with many links and it just uses UpdateLinks:=3 and opens the file and displays to the user the UpdateLinks prompt given by Excel.
Basically, is there a way to only open a workbook and update all its links without prompting the user with any popups and without opening up any other workbooks?
I hope that makes sense...
Thanks,
I currently have macro which does the following:
Reads from a master list of files to determine which individual files the user wants to open and then check certain things in each file. Each of these files are password protected (most with the same password). I have code which allows the user to enter a password into a MsgBox at the beginning of the macro (call it MyPassword). The macro then uses this password as it goes forward and opens each of the files (so the user doesn't keep getting password popups each time a new file is opened).
However, some of these indivdual files have many links to other password protected workbooks within them. So, for a time, here is the work around I coded (remember the intent here was that the user would not have to enter in any prompts after the initial kickoff, aka they wouldn't have to sit there and babysit the macro, they could kick it off for many files on one machine and walk away):
- open up the individual file (call it FileA) using the MyPassword variable. Then determine all the links within this file. I could not figure out a way to have the VBA code automatically enter in MyPassword into the UpdateLinks prompt that the user gets when opening a file with links. So my workaround was to open up each of the link files (using the MyPassword variable) and activating each of these so the links in FileA would get updated and then closing all the link files. Then I could move forward with my checks.
However, some of these files have become filled with so many links it takes a very long time to open up all the link files. When link files have their own links, it just goes on and on and on. So for these type of files, I have the macro determine if it is one of these files with many links and it just uses UpdateLinks:=3 and opens the file and displays to the user the UpdateLinks prompt given by Excel.
Basically, is there a way to only open a workbook and update all its links without prompting the user with any popups and without opening up any other workbooks?
I hope that makes sense...
Thanks,
