Macro: open files in multiple folders and run defined macro

[jcrawford]

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

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

 

[Smitty]

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]

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]

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.

 

[jcrawford]

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.

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