Workbooks.Open in VBA - Getting Password Popup Window

[PoeticRobot]

I have the below code, which I will use to open a file. I will then gather data about the file and report back in a master workbook.

My issue comes when I am trying to open a file which has a password:

• The first Workbooks.Open method presents me with a pop-up window, which I expect, although I will need to suppress this pop-up window if the (known incorrect) password does not open the workbook.
• The second Workbooks.Open uses the password parameter, and I know the password used is correct, but I am still being asked to enter the password, rather than VBA opening the workbook.

Is there something I am doing wrong, as I can't see why my workbook isn't opening automatically.

For clarity: intPassword is going to be used to define whether the workbook I am opening has a password, and if so, if the password saved as strPass is correct.

0 = No Password, 1 = Password (strPass), 2 = Password (Unknown)​

Public Sub Main()

Dim strFilePath As String
Dim strFileName As String
Dim wbkTarget As Workbook
Dim intPassword As Integer       'Code to show if file is password protected, and do I know the password. 
Dim strPass As String

strFilePath = "MyFilePath"
strFileName = "MyFileName"
strPass = "Password"

On Error GoTo Proc_Err

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wbkTarget = Nothing
On Error Resume Next

Application.EnableEvents = False        'Prevent OnOpen Events in wbkTarget

Set wbkTarget = Workbooks.Open(Filename:=strFilePath & strFileName)
    If Not wbkTarget Is Nothing Then
        intPassword = 0
        GoTo FileOpen
    End If
    
Set wbkTarget = Workbooks.Open(Filename:=strFilePath & strFileName, UpdateLinks:=False, Password:="strPass", IgnoreReadOnlyRecommended:=True)
    If Not wbkTarget Is Nothing Then
        intPassword = 1
FileOpen:
    'Code goes here to get details of open file
    
wbkTarget.Close

Else
    intPassword = 2
    With ThisWorkbook.Sheets("Sheet1")
        'Process Details
    End With
End If

    Application.EnableEvents = True
    
Set wbkTarget = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True

Proc_Exit:
Exit Sub
Proc_Err:
Debug.Print Err.Description
Resume Proc_Exit

End Sub

I am using Office 365 ProPlus on Windows 10.

 

[jasonb75]

The password pop-up is more of an interface than an alert so I doubt that it can be suppressed.

On the second Open, you have double inverted commas that shouldn't be there.

Set wbkTarget = Workbooks.Open(Filename:=strFilePath & strFileName, UpdateLinks:=False, Password:="strPass", IgnoreReadOnlyRecommended:=True)

It should be strPass not "strPass"
The way you have entered it, vba is entering an incorrect password, which is probably why the box is still showing and the file not opening.

 

[PoeticRobot]

Hi Jason,

The inverted commas is an error I had picked up already, I just forgot to remove them from the code that I was pasting here.
Either way I enter the password, either using strPass, or by entering the password inside inverted commas, I am still presented with a pop-up box.

 

[jasonb75]

Was the workbook protected from within excel, or from windows explorer?

As far as I can see, your code should open the file correctly if the workbook was protected from the excel Review tab, if it was saved as read only with password protection from the windows file browser then I think that what you need might be out of the scope of vba password opening.

 

[PoeticRobot]

The workbook was saved with a password to modify. After speaking to some people in the office, it seems that the problem may be caused by MS Azure Information Protection (AIP), which has recently been installed on all machines.

I agree that the code itself should be enough to open the file, so I'm going to try to find out if AIP can prevent VBA from opening a protected file.

 

[jasonb75]

I've only had a very quick look, but cross searching vba and azure on google, although there were no useful results for what you need to do, the things that I did look at suggest that the 2 don't play well together.

 

[PoeticRobot]

I've answered this myself. I found that rather than using

Password:=strPass

, if I used

WriteResPassword:=strPass

the file opens as expected.