Password array to open all password protected workbook in a folder

Hi -

Look for

Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0,password:=password)

Regards,
Event

But my password is different for each of the workbooks. The for loop will run the same password for all the workbooks which is invaild

Hi -

Put the password in variable, do a case statement or the like.

Regards,
Event

hi, can you be more specific? Sorry my vba is not good

Hi -

How is your file name constructed?


Regards,
Event

Hi -

Your code is not supported starting 2007 version, try this instead

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook, pwd As String, f As Object


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With CreateObject("Scripting.FileSystemObject")
Set f = .GetFolder("G:\Test\Test")
    For Each file In f.Files
        Select Case file.Name
            Case "Book1.xls", "Book2.xls"
                pwd = "123"
            Case "Book3.xls", "Book4.xls", "Book5.xls"
                pwd = "321"
        End Select
        Set wbResults = Workbooks.Open(file, UpdateLinks:=0, Password:=pwd)
        
        'MY CODE
        
        wbResults.Close SaveChanges:=True
    Next
Set f = Nothing
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Regards,
Event

Hi, thanks for your help but I think i solved my problem. Here's the solution to it. Have a new sheet which store password for all the different workbooks and then add it into the for loop

Dim RowNum As Integer
RowNum = 2
Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "G:\Test\Test"
            .FileType = msoFileTypeExcelWorkbooks
            .Filename = "Book*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 
                        pwd = Sheets("PasswordTable").Range("B" & RowNum).Value
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0, Password:=pwd)
                 'MY CODE
                        wbResults.Close SaveChanges:=True
                        
                        RowNum = RowNum + 1
                    Next lCount
                End If
        End With

Hi -

Glad you solved it. 2 things I can comment

1. If you get time to upgrade to 2007 and above, the file search will not work. so bookmark this thread for your future reference.
2. Try renaming the file as well as your sheet wherein your password stored and see if the password still synced on the loop being open by the your script

Regards,
Event

Thanks for you advice. The file that excels open is in alphabetical order which means my password array for the file name must be in alphabetical order in order to execute properly. but no worries a simple macro will help me sort my file name and password table in ascending order to meet the order which excel opens my file. Thanks alot!