VBA code woring all all files under one directory except one

[dennisli]

Good afternoon,

The following slightly changed code I copied from Ozgrid. The following paragraph is also copied from Ozgrid:

Let's now use a Loop to loop through all Excel Workbooks that are in a specified folder, open them, do some stuff, then close them. This sort of code is very handy when you have many Workbooks that need the same code run on them, all you need to do is place them all in the same folder.

What I need is that the code can work on all the files under the same directory except one file named Source2.

Any ideas will be highly appreciated.

Dennis



Sub RunCodeOnAllXLSFiles()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim ws As Worksheet

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

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = "C:\Dell\DataSource"
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then 'Workbooks in folder
For i = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(.FoundFiles(i))

'Do Your Code Here
For Each ws In wbResults.Worksheets

ws.Activate

ws.Cells.Replace What:="2005", _
Replacement:="2006", _
LookAt:=xlPart

Next ws

ActiveWorkbook.Save
ActiveWorkbook.Close

Next i
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[Norie]

Just put an If statement in the loop.

Perhaps something like this?

For i = 1 To .FoundFiles.Count
If Dir(.FoundFiles(i)) <>"Source2.xls" Then 
     Set wbResults = Workbooks.Open(.FoundFiles(i))

     'Do Your Code Here
      For Each ws In wbResults.Worksheets

           ws.Cells.Replace What:="2005", Replacement:="2006", LookAt:=xlPart

     Next ws 
End If
Next i

 

[dennisli]

Your are always right, Norie.
Have a good day.