CODEWITHSUNDEEP
excelvba
James Williams
James Williams

Reputation: 5

Open Excel File with password in vba loop

I've got a loop that is scraping data from 100+ workbooks. Some have password protection for the workbooks and worksheets.

I thought I'd added the code to enter the password for each workbook and it was working until I changed the code to look in the folder and its subfolders, rather than a single folder but now all the files with passwords give a pop up to enter the password.

I assume I'm not entering the password at the right point. I've tried moving it before the "For each OFile' etc. but I'm not sure how to do so before I've set 'wb' without giving errors.

Any help would be greatly appreciated!

Sub Test_Macro()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim fso, oFolder, oSubfolder, OFile, queue As Collection
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection
    queue.Add fso.GetFolder("D:\example\example")
'^^^ UPDATE THIS FILE PATH TO FOLDER WHERE THE RETURNED SCORECARDS ARE STORED.

Do While queue.Count > 0
Set oFolder = queue(1)
queue.Remove 1 'dequeue
For Each oSubfolder In oFolder.SubFolders
queue.Add oSubfolder
Next oSubfolder

For Each OFile In oFolder.Files

y = ThisWorkbook.Sheets("Consol").Cells(Rows.Count, 1).End(xlUp).Row + 1 '<<< Finda next available row containing a value in consol sheet

Set wb = Workbooks.Open(OFile.path) '<<< Sets variable to the open performance scorecard and unprotects
wb.Password = "Password" '<<<<This is one that doesn't appear to be working

Set ws = wb.Sheets("Detailed Summary") '<<< Defines sheet in the open scorecard to scrape from

ws.Unprotect Password:="Password"

wsLR = ws.Columns("B").Find("*", after:=ws.Cells(1, 2), SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row '<<< Defines the range containing data in the open scorecard
Dim scraped As Variant
With ws
scraped = .Range(.Cells(5, 2), .Cells(wsLR, 85))
End With

Dim consolRng As Range
Set consolRng = ThisWorkbook.Sheets("Consol").Cells(y, 1)

Set consolRng = consolRng.Resize(rowsize:=UBound(scraped, 1), columnsize:=UBound(scraped, 2))
consolRng = scraped

wb.Close (Saved = True)

Next OFile

Loop

End Sub

Any help would be greatly appreciated!

Upvotes: 0

Views: 4389

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

According to MSDN documentation, one of the arguments to Open method is a password.

Set wbk = Workbooks.Open(FileName:=yourfilename, Password:=yourPassword)

Upvotes: 1

Related Questions