Hi guys, I've currently 10 excel password protected workbook in a shared drive with a master workbook with datas from each of the 10 workbook. Is it possible for me to create a macro in the master workbook whereby when the macro is invoked, all of the worksheets in all the 10 workbook will do what my code says? Currently my code is like what is shown below but it will prompt me password for all 10 workbook. Is it possible to have an array of password or something? such that I will not need to key in password after password for each of the10 workbooks individually. Thanks in advance.
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
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 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
'MY CODE
wbResults.Close SaveChanges:= True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Bookmarks