Help VBA code to update password protected files

[Rudi]

Hi all,

Please help...

The code below updates links to source files with passwords. It was working fine with only one source file, but know the workbook contains another linked file with another password. I need help to modify the code to prompt for each new password so that all the linked files get updates.

Code: Select all

Sub OpenAndUpdate()
    Application.ScreenUpdating = False
    On Error GoTo EH
    Dim myPass As String
    Dim varLink
    Dim wbk As Workbook
ResumeCode:
    For Each varLink In ThisWorkbook.LinkSources(xlExcelLinks)
        Set wbk = Workbooks.Open(Filename:=varLink, Password:=myPass, UpdateLinks:=xlUpdateLinksNever)
        Calculate
        wbk.Close False
    Next varLink
    Application.ScreenUpdating = True
    Exit Sub
EH:
    myPass = InputBox("Supply the next password.", "Link File Password")
    GoTo ResumeCode
End Sub

TIA

[HansV]

Change the line

GoTo ResumeCode

to

Resume

[Rudi]

It works great. TX

PS: Is there any way to hide the password typed into the inputbox with asterix??

[HansV]

Not with the InputBox function. You could create a small userform that imitates an input box, with a text box, an OK button and a Cancel button. You can set the PasswordChar property of the text box to *. Declare strPassword as a public variable at the top of your code module, and set its value in the On Click event procedure of the OK button on the userform.

[Rudi]

TX. I will keep that in mind if the recipient asks for this.

PS: I have made some additional improvements to the code. (See below). It is working great now! Cheers

Code: Select all

Sub OpenAndUpdate()
    Application.ScreenUpdating = False
    On Error GoTo EH
    Dim myPass As String
    Dim varLink
    Dim wbk As Workbook
    For Each varLink In ThisWorkbook.LinkSources(xlExcelLinks)
        Set wbk = Workbooks.Open(Filename:=varLink, Password:=myPass, UpdateLinks:=xlUpdateLinksNever)
        Calculate
        wbk.Close False
    Next varLink
    Application.ScreenUpdating = True
    Exit Sub
EH:
    myPass = InputBox("Supply password for: " & vbNewLine & varLink, "Link File Password")
    If myPass = "" Then Exit Sub
    Resume
End Sub

[Rudi]

Hi...another need for assistance.

The code below prompts the user for each password of the linked files in order to update the destination file. The code was originally created to update from only one source file...and at this point these was a constant variable called cstrPASSWORD = "pass"...that provided the code with the password to read the source file. I since modified the code by adding the Error Handler to prompt the user for a password when I modified the code to update two source linked files. Now the user wants this code to update from (up to 20+) workbooks...each of which has a different password. Obviously they do not want to type a password dynamically for 20+ files, so the idea is to store the passwords in the code and have the macro open and update without prompting for the password.

How do I do this? Can I create a constant for each workbook and store the password in the code and loop through updating from each source file? I need assistance to set up something like this....I guess an array varialbe that can store 20 passwords and loop through the array??

TIA for any help.

Code: Select all

Sub OpenAndUpdate()
    Application.ScreenUpdating = False
    On Error GoTo EH
    Dim myPass As String
    Dim varLink
    Dim wbk As Workbook
    For Each varLink In ThisWorkbook.LinkSources(xlExcelLinks)
        Set wbk = Workbooks.Open(Filename:=varLink, Password:=myPass, UpdateLinks:=xlUpdateLinksNever)
        Calculate
        wbk.Close False
    Next varLink
    Application.ScreenUpdating = True
    Exit Sub
EH:
    myPass = InputBox("Supply password for: " & vbNewLine & varLink, "Link File Password")
    If myPass = "" Then Exit Sub
    Resume
End Sub

[HansV]

You could use an array, but I'd place the workbook names and passwords in a (very hidden) worksheet. You can then look up the password from the workbook name, e.g.

Code: Select all

Sub OpenAndUpdate()
    Application.ScreenUpdating = False
    On Error GoTo EH
    Dim myPass As String
    Dim varLink
    Dim wbk As Workbook
    For Each varLink In ThisWorkbook.LinkSources(xlExcelLinks)
        myPass = Application.VLookup(varLink, Worksheets("Hidden Password Sheet").Range("A1:B100"), 2, False)
        Set wbk = Workbooks.Open(Filename:=varLink, Password:=myPass, UpdateLinks:=xlUpdateLinksNever)
        Calculate
        wbk.Close False
    Next varLink
    Application.ScreenUpdating = True
    Exit Sub
EH:
    myPass = InputBox("Supply password for: " & vbNewLine & varLink, "Link File Password")
    If myPass = "" Then Exit Sub
    Resume
End Sub

[Rudi]

Hi,

That is a good suggestion.

I am having a problem with the line:
myPass = Application.WorksheetFunction.VLookup(varLink, Worksheets("PW").Range("A1:B5"), 2, False)
Note: I added the worksheetfunction!
With this I get the error: Unable to get the VLookup property of the worksheetFunction class
Without it I get error: Type Mismatch

Any ideas?

Thanks for your patience.

[HansV]

Application.VLookup is shorthand for Application.WorksheetFunction.VLookup (there is a difference in the way errors are handled).
The error message means that there is no exact match for the workbook name + path in column A of the PW sheet.

[Rudi]

Thanks for guidance...I work on that!
Cheers