VBA to enter password when prompted

[BrianG86]

Hi all,

I have a looping code to open workbooks and copy then paste data to another workbook. Each workbook I open has a password. They are all the same password, how do I get it to enter automatically?

Here is my code so far

Sub OpenAndClearContentsInColumnAA()

    Dim strF As String, strP As String
    Dim wb As Workbook
    Dim ws As Worksheet




'Edit this declaration to your folder name
strP = "T:\Lender Updates\Lender Updates\Lloyds\Paid" 'change for the path of your folder




strF = Dir(strP & "\*.xlsm") 'Change as required




Do While strF <> vbNullString




    Set wb = Workbooks.Open(strP & "\" & strF)
    Set ws = wb.Sheets("Paid") 'uses first sheet or if all the same names then ws.Sheets("yoursheet")
    ws.Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThisWorkbook.Activate
    ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial
    wb.Close True
    
    strF = Dir()
Loop




End Sub

 

[dmt32]

Hi,
just include the password (case sensitive) where shown in your workbooks.Open method

Set wb = Workbooks.Open(strP & "\" & strF, Password:="mypasswordhere")

Dave

 

[BrianG86]

Excellent,

Thanks for your reply.

Brian