On error try different password VBA

[ExcelHobbit]

I have a piece of VBA code that opens a workbook that is password encrypted, but could be one of three different passwords:

Workbooks.Open Filename:="\\Drive1\Folder1\test.xlsx", Password:="Test1"

Workbooks.Open Filename:="\\Drive1\Folder1\test.xlsx", Password:="Test2"

Workbooks.Open Filename:="\\Drive1\Folder1\test.xlsx", Password:="Test3"

if it isn't the first password, i need it to try the second or third password. is there an On error method of doing this?

 

[dmt32]

Hi,
try following & see if helps you

Sub OpenWorkbook()
    Dim wb As Workbook
    Dim FileName As String
    Dim Password As Variant
   
    FileName = "\\Drive1\Folder1\test.xlsx"
    
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each Password In Array("Test1", "Test2", "Test3")
        Set wb = Workbooks.Open(FileName, False, False, , Password)
        If Not wb Is Nothing Then Exit For
    Next Password
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    If wb Is Nothing Then MsgBox "Password Not Valid", 64, "Error"
End Sub

Do be mindful that passwords are case sensitive

Dave