Excel VBA: How to dismiss password boxes for linked files in Macro

Question

I am trying to dismiss pop up password boxes while my macro is running, each file is password protected which i have code for to unlock these however the files also are linked to other password protected files that excel prompts me for a password, instead of clicking cancel every time one of these boxes pops up is there a way to dismiss the password boxes in the macro?

Here is my current code:

Sub OpenCurrentGBP()

cdirectory = Range("E5").Value
Mdirectory = Range("E6").Value

cGap = Range("E11").Value
cEVE = Range("E12").Value
cHedge = Range("E13").Value
cVarFile = Range("E16").Value
cGapMovements = Range("E17").Value
cQRMCheck = Range("E18").Value

GapPwd = Range("E42").Value
EVEPwd = Range("E43").Value
HedgePwd = Range("E44").Value
EurogapPwd = Range("E45").Value
EuroEVEPwd = Range("E46").Value
VarPwd = Range("E47").Value
MovPwd = Range("E48").Value

Application.DisplayAlerts = False

Call OpenFile(cdirectory, cGapMovements, MovPwd)
Call OpenFile(cdirectory, cGap, GapPwd)
Call OpenFile(cdirectory, cEVE, EVEPwd)
Call OpenFile(cdirectory, cHedge, HedgePwd)
Call OpenFile(cdirectory, cVarFile, VarPwd)
Call OpenFile(cdirectory, cQRMCheck)

Application.DisplayAlerts = True

End Sub

The OpenFile Macro is as follows:

Sub OpenFile(Directory, File, Optional Pass)
On Error GoTo Failure

Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

If IsMissing(Pass) = 0 Then

Workbooks.Open Filename:= _
Directory & "\" & File, Notify:=False, Password:=Pass
Else
Workbooks.Open Filename:= _
Directory & "\" & File, Notify:=False
End If

Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

Exit Sub
Failure:     MsgBox (File & " could not be opened")

Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

End Sub

You are not showing all code essential to the question. My guess is that procedure OpenFile is not using the Password argument of the workbooks.open statement. — jkpieterse

Rafael Osipov Rafael Osipov · Accepted Answer · 2018-01-08T13:57:27

Inside the subs you call (Next time you should show them here too) to open the protected Workbooks you need to use the Workbooks.open statment and I have an example of my own codes that I am using to open workbooks with password:

Workbooks.Open Filename:=tmp_file_p, Password:="7399"

tmp_file_p is a string variable with the path and the name of the workbook.

Excel VBA: How to dismiss password boxes for linked files in Macro

1 Answers