Workbooks.Open in VBA - Getting Password Popup Window

PoeticRobot

New Member
Joined
Nov 19, 2019
Messages
10
Office Version
365
Platform
Windows
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)​

VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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.
VBA Code:
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

New Member
Joined
Nov 19, 2019
Messages
10
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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

New Member
Joined
Nov 19, 2019
Messages
10
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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

New Member
Joined
Nov 19, 2019
Messages
10
Office Version
365
Platform
Windows
I've answered this myself. I found that rather than using
VBA Code:
Password:=strPass
, if I used
Code:
WriteResPassword:=strPass
the file opens as expected.
 

Forum statistics

Threads
1,078,437
Messages
5,340,270
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top