[Excel VBA] How to Open a Password-Protected Excel Workbook

When manipulating other workbooks with VBA, you may encounter files protected by a “Read Password.” It is possible to open these workbooks automatically by letting VBA handle the password entry.

In this article, I will explain the basic method using the Password argument of the Workbooks.Open method, as well as a recommended method for handling passwords more securely.

Basic Code: Opening a Workbook by Specifying a Password

First, here is the basic method where you write the password directly into the VBA code.

Sub OpenProtectedWorkbook_Basic()

    ' Declare variables
    Dim filePath As String
    Dim filePassword As String
    Dim protectedBook As Workbook
    
    ' Specify the path of the workbook you want to open
    filePath = ThisWorkbook.Path & "\Protected_Data.xlsx"
    
    ' [WARNING] Direct hardcoding of the password in VBA
    filePassword = "secret123"
    
    ' Open the workbook by specifying the Password argument
    Set protectedBook = Workbooks.Open(Filename:=filePath, Password:=filePassword)
    
    ' --- Process after opening ---
    If Not protectedBook Is Nothing Then
        MsgBox "Opened [" & protectedBook.Name & "]."
        protectedBook.Close SaveChanges:=False ' Close without saving for this example
        Set protectedBook = Nothing
    End If
    
End Sub

Explanation of the Code

When executing the Workbooks.Open method, you specify the password as a string for the named argument Password.

Workbooks.Open(Filename:="File Path", Password:="Password")

This allows you to open the file directly without displaying the password entry dialog box.

[Important] Security Note Regarding Password Handling

Writing the password directly into the VBA code (hardcoding), as shown in the basic code above, is extremely dangerous from a security perspective.

Anyone who can open the VBE (VBA editor screen) can read the password in plain text from the code. Even if you password-protect the VBA project itself, it only provides weak protection.

Therefore, avoid hardcoding passwords. Instead, I strongly recommend the following method, which prompts for input when the macro is executed.

[Recommended] How to Enter Passwords Securely Using InputBox

A safer method is to display an InputBox when the macro runs, asking the user to enter the password each time. This way, no password remains in the file.

Code Example

Sub OpenProtectedWorkbook_Secure()

    ' Declare variables
    Dim filePath As String
    Dim userInputPassword As String
    Dim protectedBook As Workbook

    ' Specify the path of the workbook you want to open
    filePath = ThisWorkbook.Path & "\Protected_Data.xlsx"
    
    ' Display InputBox to let the user enter the password
    userInputPassword = InputBox("Please enter the password for the protected workbook:", "Password Input")
    
    ' Exit if Cancel is pressed or input is empty
    If userInputPassword = "" Then Exit Sub
    
    ' Prepare to handle errors in case the password is incorrect
    On Error Resume Next
    
    ' Attempt to open the workbook with the entered password
    Set protectedBook = Workbooks.Open(Filename:=filePath, Password:=userInputPassword)
    
    ' Handle errors (e.g., incorrect password)
    If Err.Number <> 0 Then
        MsgBox "Incorrect password or could not open the file.", vbCritical
        Exit Sub
    End If
    
    ' Reset error handling
    On Error GoTo 0
    
    ' --- Process after opening ---
    MsgBox "Successfully opened [" & protectedBook.Name & "]."
    protectedBook.Close SaveChanges:=False
    ' ---------------------------
    
    Set protectedBook = Nothing

End Sub

Explanation of the Code

• InputBox(...): Displays a dialog box prompting the user for text input and returns the entered value.
• On Error Resume Next: An instruction to continue to the next line without stopping processing even if an error occurs in VBA. Since Workbooks.Open generates an error if the password is incorrect, this is used to detect and handle the error gracefully.

Summary

To open a workbook with a password, use the Password argument of the Workbooks.Open method.

• Basic Method: Write the password directly in the code, but this is deprecated due to high security risks.
• Recommended Method: Use InputBox to have the user enter the password at runtime. This allows for secure processing without leaving the password inside the file.

When dealing with files containing confidential information, be sure to adopt the safer method.