none
Workbooks.Open in VBA - Getting Password Popup Window RRS feed

  • Question

  • 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. 

    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.

    I am using Office 365 ProPlus on Windows 10.

    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:="miteam", 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
    
    

    Monday, November 18, 2019 4:26 PM

All replies

  • Hi NickShep85,

    I'm not sure why you're using two variables, "intPassword" and "strPass".
    Try to skip the prompt screen for password.
    On Error Resume Next
    SendKeys "{ESC}" ' --<< skip password screen Set wbkTarget = Workbooks.Open(Filename:=strFilePath & strFileName) If Not wbkTarget Is Nothing Then intPassword = 0 GoTo FileOpen End If
    Regards,

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Tuesday, November 19, 2019 12:19 AM add more
    Monday, November 18, 2019 11:35 PM