Excel VBA Code to Run Macro on Multiple Excel Files

October 30, 2017

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. 

 

 

 

 

 

 

 

 

 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com