MrExcel Publishing
Your One Stop for Excel Tips & Solutions

run a macro in all excel file in a folder


Posted by steve w on September 03, 2001 10:56 AM

I want a certain macro to run on all excel files in a certain folder when executed
anyone have an idea
steve w


Posted by Dax on September 03, 2001 3:04 PM

Steve,

You could do this using Excel's in built Filesearch capability. Here's an example:-

Sub GetAllExcelFiles()
Dim sFolder As String
Dim lCounter As Long

sFolder = "C:\"

With Application.FileSearch
.NewSearch
.LookIn = sFolder
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True 'Set to false or exclude if you don't want subfolders
.Execute
End With

For lCounter = 1 To Application.FileSearch.FoundFiles.Count
Call MyRoutine(Application.FileSearch.FoundFiles(lCounter))
Next lCounter
End Sub

Sub MyRoutine(sExcelFile As String)
'You could now process the file in any way you want e.g.
Workbooks.Open sExcelFile
ActiveWorkbook.Sheets(1).Cells(1, 1) = "Processed!"
End Sub

Hope it helps,
Dax.