Excel VBA Code to Run Macro on Multiple Excel Files

wigi from Brussels has posted some very simple VBA code here, which can be used to run a macro on multiple other Excel files.

Sub LoopThroughFiles() FolderName = "C:\Folder1\" If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator Fname = Dir(FolderName & "*.xls")

'loop through the files Do While Len(Fname)

With Workbooks.Open(FolderName & Fname)

' here comes the code for the operations on every file the code finds

End With

' go to the next file in the folder Fname = Dir

Loop End Sub

In Tonight's example I'm going to show how this can be used to run a macro which will split a workbook into individual worksheets, which was the Tip of the Night for April 11, 2016.

We start with a folder of multiple Excel files, some of which have more than worksheet.

Enter the vba code in a new module. On the line beginning FolderName list the path for the folder containing the Excel files you need to process.

Enter the body (without the beginning Sub heading and the End Sub footer) of the VBA code you want to run on multiple files after the comment line beginning: 'here come the code . . . and before where it reads, 'End With'.

The combine VBA codes process all of the files creating multiple .csv files with the data from each source worksheet.