using macros across multiple excel files

[coriolis]

I work in research and I frequently need to process large amounts of data. Each data set is output into an excel spreadsheet and I have ~150 spreadsheets (formatted identically) that I now need to process.

I recorded a macro to manipulate one of the data sheets, and I know how to use the same macro for each individual file, but I'm curious if I can automate the process further.

Is it possible to record and run a macro that is capable of automatically searching through a folder of files and performing the same operations in each?

 

[Domenic]

Try...

<font face=Courier New><SPAN style="color:#007F00">'Force the explicit declaration of variables</SPAN><br><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Test()<br>    <br>    <SPAN style="color:#007F00">'Declare the variables</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> objFSO <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> objFolder <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> objFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wkbSource <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <br>    <SPAN style="color:#007F00">'Turn off ScreenUpdating</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#007F00">'Specify the path to the folder (change the path accordingly)</SPAN><br>    strPath = "C:\Users\Domenic\Desktop\"<br>    <br>    <SPAN style="color:#007F00">'Create an instance of the FileSystemObject</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> objFSO = CreateObject("Scripting.FileSystemObject")<br>    <br>    <SPAN style="color:#007F00">'Get the folder</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> objFolder = objFSO.GetFolder(strPath)<br>    <br>    <SPAN style="color:#007F00">'Loop through each file in the folder</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> objFile <SPAN style="color:#00007F">In</SPAN> objFolder.Files<br>    <br>        <SPAN style="color:#00007F">If</SPAN> Right(objFile.Name, 4) = ".xls" <SPAN style="color:#00007F">Then</SPAN><br>    <br>            <SPAN style="color:#007F00">'Open the current file</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wkbSource = Workbooks.Open(objFile.Path)<br>            <br>            <SPAN style="color:#007F00">'Do stuff</SPAN><br>            <SPAN style="color:#007F00">'</SPAN><br>            <SPAN style="color:#007F00">'</SPAN><br>            <SPAN style="color:#007F00">'</SPAN><br>            <br>            <SPAN style="color:#007F00">'Close the current file, without saving it</SPAN><br>            wkbSource.Close savechanges:=<SPAN style="color:#00007F">False</SPAN><br>            <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">Next</SPAN> objFile<br>    <br>    <SPAN style="color:#007F00">'Turn ScreenUpdating back on</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[coriolis]

Thanks so much!

 

[Domenic]

You're very welcome!