(Solved) – Workbooks.Open is not using the provided Password, but instead requesting password from user

(solved)-–-workbooks.open-is-not-using-the-provided-password,-but-instead-requesting-password-from-user

I am opening a whole bunch of workbooks that all have the same password in order to check their sheets.

The program starts with a dummy password that will fail, and when it does a userform (named PasswordUserform) is shown in order to capture the password for all of the files. Subsequently, each workbook is opened via that password. As such, it should A) never fail, and B) never use the default password entry userform.

Private Function CheckFiles(ByRef WkbApp As Excel.Application, _
            WkbFolder As String, _
            FoundFile As String, _
            NeededSheets As Scripting.Dictionary, _
            ByRef Pass As String) As Boolean

    Dim Wkb As Workbook

    WkbApp.Visible = False

    ' Attempts to open the workbook. If it fails, then attempts to get a
    '  better password from the user. This password is saved for further
    '  attempts, but only during runtime.
    On Error Resume Next
        Set Wkb = WkbApp.Workbooks.Open(Filename:=WkbFolder & FoundFile, _
                    Password:=Pass)

        If Err.Number > 0 Then
            PasswordUserform.FileNameLabel.Caption = "'" & _
                    Left(FoundFile, 20) & "...'"
            PasswordUserform.Show

            Pass = PasswordUserform.PasswordBox.Text
            Unload PasswordUserform

            Set Wkb = WkbApp.Workbooks.Open(Filename:=WkbFolder & FoundFile, _
                        Password:=Pass)
        End If

    On Error GoTo 0

    ' Do Stuff within workbook, Setting function to True or False depending on contents
End Function

The function is called within a simple Do While loop:

    Do While Len(FoundFile) > 0
        FoundDate = FileDateTime(WkbFolder & FoundFile)
        If FoundDate > LatestDate And CheckFiles(WkbApp, WkbFolder, _
                    FoundFile, NeededSheets, Pass) Then
            LatestDate = FoundDate
            LatestFile = FoundFile
            LatestPass = Pass
        End If

        FoundFile = Dir
    Loop

When stepping through, the first Set Wkb may or may not display the default password userform. If that pops up and is cancelled, the custom PasswordUserform displays. On proper entry, the second Set Wkb (in the IF scope) always displays the default password userform. But on the next file, the password is used properly and no userform is displayed. Finally, the occasional file will create the default password userform (on inputting the password that should have been provided, it opens fine, as all files in the folder use the same password).

I’ve tried setting the WriteResPassword as well, with no difference. Additionally, DisplayAlerts=False does not prevent the problem.

Leave a Reply

Your email address will not be published. Required fields are marked *