VBA code woring all all files under one directory except one

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Just put an If statement in the loop.

Perhaps something like this?
Code:
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
 

Forum statistics

Threads
1,137,060
Messages
5,679,385
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top