Macro: open files in multiple folders and run defined macro

jcrawford

New Member
Joined
Sep 30, 2008
Messages
3
What I am trying to accomplish is have a macro that runs from "personal" searches through multiple folders and sub folders, which are set up identical for all agents, and runs the predefined macro on each workbook in a folder with a defined name.

What I would like the macro to open, run, save and close for each sales agent for the current period.

The code posted below runs the macro current if I input the name of the final address and run the macro but I would like to automate this step. Any thoughts or comments are appreciated.

Example Address: U:\Sales Folder\Year\Working Folders\ Division Folder\Sales Agent Folder\Current Period

Wanted effect: U:\Sales Folder\Year\Working Folders\*\*\Current Period

Code:
Sub RunCodeOnAllXLSFiles()

MyFile = "File Address Here"

Dim lCount As Long

Dim wbResults As Workbook

Dim wbCodeBook As Workbook



Application.ScreenUpdating = False

Application.DisplayAlerts = False

Application.EnableEvents = False



On Error Resume Next



Set wbCodeBook = activeworkbook



    With Application.FileSearch

        .NewSearch

         'Change path to suit

        .LookIn = MyFile

        .FileType = msoFileTypeExcelWorkbooks

        .Filename = "*.xls"

        

            If .Execute > 0 Then 'Workbooks in folder

                For lCount = 1 To .FoundFiles.count 'Loop through all.

                 'Open Workbook x and Set a Workbook variable to it

                 Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

                                  

                 Call My Macro

                 

                 wbResults.Close SaveChanges:=True

             

                 Next lCount

            End If

    End With

    

    On Error GoTo 0

    Application.ScreenUpdating = True

    Application.DisplayAlerts = True

    Application.EnableEvents = True

End Sub
 
Last edited by a moderator:

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

How can you tell the macro what the Division, Sales Agent and Current Period are? Are they stored in a workbook somewhere? If so you can reference cells ranges in the file path like this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> RunCodeOnAllXLSFiles()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wbResults <SPAN style="color:#00007F">As</SPAN> Workbook, wbCodeBook <SPAN style="color:#00007F">As</SPAN> Workbook<br><br>        <SPAN style="color:#00007F">With</SPAN> Application<br>            .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>            .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>            .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br><br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br><br>            <SPAN style="color:#00007F">Set</SPAN> wbCodeBook = ActiveWorkbook<br>            <SPAN style="color:#00007F">Set</SPAN> ws = Sheets("Sheet1")<br>            <SPAN style="color:#007F00">'Change path to suit</SPAN><br>            MyFile = "U:\Sales Folder\Year\Working Folders\" & ws.Range("A1") & "\" & ws.Range("A2") & "\Current Period"<br><br>            <SPAN style="color:#00007F">With</SPAN> .FileSearch<br>                .NewSearch<br>                .LookIn = MyFile<br>                .FileType = msoFileTypeExcelWorkbooks<br>                .Filename = "*.xls"<br>                    <SPAN style="color:#00007F">If</SPAN> .Execute > 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Workbooks in folder</SPAN><br>                        <SPAN style="color:#00007F">For</SPAN> lCount = 1 <SPAN style="color:#00007F">To</SPAN> .FoundFiles.Count <SPAN style="color:#007F00">'Loop through all.</SPAN><br>                            <SPAN style="color:#007F00">'Open Workbook x and Set a Workbook variable to it</SPAN><br>                            <SPAN style="color:#00007F">Set</SPAN> wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)<br>                                <SPAN style="color:#00007F">Call</SPAN> MyMacro<br>                                wbResults.Close SaveChanges:=<SPAN style="color:#00007F">True</SPAN><br>                        <SPAN style="color:#00007F">Next</SPAN> lCount<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><br>        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        .EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,
 

jcrawford

New Member
Joined
Sep 30, 2008
Messages
3
Thank you Smitty for both the warm welcome and they idea I think I still I need a little help getting there. The current code allows me to enter a single agent at a time without updating my actual code, what I would really like to do is have this loop for list of agents.

Range("A1:C500").Select

For Each Row in Selection

MyFile = "U:\Sales Folder\Year\Working Folders\" & ws.Range("A" & Row) & "\" & ws.Range("B" & Row) & "\ws.Range("C" & Row)"

Next Row


Were am I going wrong?
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
It's best off not to select if you don't have to, so you can define the loop without it. I amended it and left your original MyFile line so you could compare:

<font face=Tahoma>            <SPAN style="color:#00007F">For</SPAN> i = i <SPAN style="color:#00007F">To</SPAN> 500<br>                <SPAN style="color:#007F00">'Change path to suit</SPAN><br>                MyFile = "U:\Sales Folder\Year\Working Folders\" & ws.Range("A" & i) & "\" & ws.Range("B" & i) & "\" & ws.Range("C" & i)<br>                <SPAN style="color:#007F00">'MyFile = "U:\Sales Folder\Year\Working Folders\" & ws.Range("A" & Row) & "\" & ws.Range("B" & Row) & "\ws.Range("C" & Row)"</SPAN><br>    <br>                <SPAN style="color:#00007F">With</SPAN> .FileSearch<br>                    .NewSearch<br>                    .LookIn = MyFile<br>                    .FileType = msoFileTypeExcelWorkbooks<br>                    .Filename = "*.xls"<br>                        <SPAN style="color:#00007F">If</SPAN> .Execute > 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Workbooks in folder</SPAN><br>                            <SPAN style="color:#00007F">For</SPAN> lCount = 1 <SPAN style="color:#00007F">To</SPAN> .FoundFiles.Count <SPAN style="color:#007F00">'Loop through all.</SPAN><br>                                <SPAN style="color:#007F00">'Open Workbook x and Set a Workbook variable to it</SPAN><br>                                <SPAN style="color:#00007F">Set</SPAN> wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)<br>                                    <SPAN style="color:#00007F">Call</SPAN> MyMacro<br>                                    wbResults.Close SaveChanges:=<SPAN style="color:#00007F">True</SPAN><br>                            <SPAN style="color:#00007F">Next</SPAN> lCount<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <SPAN style="color:#00007F">Next</SPAN> i<br></FONT>

Especially note this part:

"\ws.Range("C" & Row)"

Which the VBE will interpret as a literal string, not part of your loop.

You'll also want to add ", i as Long" in your declarations section after ICount.

And you want to avoiding using variables like "Row" as it's already a VBA property.


 
Last edited:

jcrawford

New Member
Joined
Sep 30, 2008
Messages
3
Truly Amazing. Thank you Smitty it now works like a charm! For anyone else out there that needs to run a macro across multiple folders here is the final working code.

Note: Copy and pasting the name of each variable into your spreadsheet list helps prevent those pesky syntax errors. I spent an hour trying to figure out what was wrong with the code, when nothing was, because I missed a "-" no reason for anyone else to make the same mistake.

Code:
Sub RunCodeOnAllXLSFiles()
    Dim ws As Worksheet
    Dim MyFile As String
    Dim lCount As Long
    Dim i As Long
    Dim wbResults As Workbook, wbCodeBook As Workbook

        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False

            On Error Resume Next

            Set wbCodeBook = ActiveWorkbook
            Set ws = Sheets("Sheet1")
                     
            'Change path to suit
            
            LastRow = Range("A65536").End(xlUp).Row
            For i = i To LastRow
                'Change path to suit
                MyFile = "H:\Operation Analyse\2009 budgets\Yr2009 Working Folders\" _
                 & ws.Range("A" & i) & "\" & ws.Range("B" & i) & "\" & ws.Range("C" & i)
                        
            With .FileSearch
                .NewSearch
                .LookIn = MyFile
                .FileType = msoFileTypeExcelWorkbooks
                .Filename = "*.xls"
                    If .Execute > 0 Then 'Workbooks in folder
                        For lCount = 1 To .FoundFiles.count 'Loop through all.
                            'Open Workbook x and Set a Workbook variable to it
                            Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                                Call mymacro
                            
                                wbResults.Close SaveChanges:=True
                        Next lCount
                    End If
            End With

            On Error GoTo 0
            Next i
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
  
End Sub
 
Last edited by a moderator:

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top