Avoid passwords when looping through workbooks

**kchm_2000** · 10-25-2011, 01:04 AM

Hi,

I have some workbooks named password.xls which I loop through to do some clearing of contents for each sheet based on certain criteria. However, each time i run the loop, a pop up for password comes up. How do I avoid the passwords? In my password control sheet I have placed the desired password on cell A2 and file path on cell B2. Here are my codes for looping through workbooks. Thanks for any help in advance

Sub RunCodeOnAllXLSFilesClear()
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 = Cells(2, 2)
            .FileType = msoFileTypeAllFiles
            'Optional filter with wildcard
            '.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)
                        
                        'DO YOUR CODE HERE
                       
                       Call clearcontents
                       
                        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

and my code for each sheet

Sub clearcontents()

Dim S_heet As Worksheet
    For Each S_heet In ActiveWorkbook.Sheets
        If Left(S_heet.Name, 3) = "ACD" Then
            With S_heet
                .Columns("F:G").clearcontents
                .Columns("D:D").clearcontents
            End With
        End If
    Next S_heet
End Sub