excel macro programming....

[rpatel18]

I have the following macro in excel..
Sub replace()
'
' replace Macro
' Macro recorded 6/18/2007 by LISC USER
'

Range("B1:CM4000").Select
Selection.replace What:="** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("B1:CM4000").Select
Selection.replace What:="* ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Range("B1:CM4000").Select
Selection.replace What:="--", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Range("B1:CM4000").Select
Selection.replace What:="* ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWorkbook.Save
End Sub

the macro works great but I have to do it for 50 files all in the same folder(C:\dbase). I know there is a way to loop it and tell it to open, run the macro, save and close, but dont quite remember how to do it.

 

[SkipVought]

Hi,

Use a FileScripting Object to define the folder and loop thru the Files collection.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

sorry...

a Scripting.FileSystemObject

Skip,
[sub]
[red][/red]
[/sub]

 

[rpatel18]

I used this macro:
Sub RunCodeOnAllXLSFiles()
Dim i As Integer
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
.LookIn = "C:\MyDocuments\TestResults"
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then
For i = 1 To .FoundFiles.Count

Set wbResults = Workbooks.Open(.FoundFiles(i))

'my codes here

Next i
End If
End With

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

but wat if all the files are csv files and not xls?