Macro to open existing workbook, run macro in the opened workbook, close it, repeat with next workbook

[Chetter]

Hi All,

I have 8 individual xlsm files. When run separately each one imports a csv file, formats it, then exports 2 csv files, each with their own variations. I'm thinking instead of opening each file, running a macro in that file, closing it without saving then moving on to the next. It would be quicker to create one main workbook that has a macro to cycle through each of the 8 files. I tried using this code:

Workbooks.Open Filename:=ThisWorkbook.Path & "\BOOK123 USE THIS ONE 2099.xlsm"
Application.Run ("'BOOK123 USE THIS ONE 2099.xlsm'!CopyPasteSave2099")

and just changing the file name and macro name for each file and repeating the code but the macro closes itself after the first two lines of code (shown above).

I read through this forum often so thought I'd join and post to get your thoughts. I figured there has to be an easier, simpler way of accomplishing this. Thank you.

 

[Joe4]

Welcome to the Board!

Is something like this what you are looking for?

Loop through files in a folder and copy data into a Master Sheet based on criteria

Hello, I use the macro below to loop through all the xlsx files with a certain name in a folder and to copy paste data from a sheet called Exchange rates into a Sheet1 of the the master file (Thisworkbook). There are many different files in the folder, however I am only interested in those...

www.mrexcel.com

 

[Chetter]

Hi Joe,

Thanks for taking a look. It's kind of what I'm looking for, I'm actually using similar code in a different of this overall process.

Trying to think of a good way to explain this.

All in the same folder I have:
8 source files. Each called 1234.xls, 2345.xls etc.....
Then I have 8 xlsm files in the same folder. Each named USE THIS ONE 1234.xlsm, USE THIS ONE 2345.xlsm etc......
Each of these xlsm files imports the source file with the same number (1234 or 2345 etc)
Right now I'm opening each xlsm file and running a macro which imports the source file, does some formatting then exports one csv file (to the same folder) then exports a second csv file (to the same folder) which is formatted differently that I use later on, then the macro closes the workbook without saving.
Instead of opening each xlsm file separately and running each file's macro, I created a separate workbook (in the same folder) that I'd like to have open each USE THIS ONE xlsm file, run the macro housed in the USE THIS ONE workbook, close that workbook and move on to the next one. Hope that makes sense. I have the code in the previous post but can't figure out how to get it to bounce back to the workbook that's trying to open and close each xlsm file. Thank you.

 

[Joe4]

I have the code in the previous post but can't figure out how to get it to bounce back to the workbook that's trying to open and close each xlsm file.

I like to use Workbook variables to capture that.

So, at the very beginning of the code, before you open any other files, add code in like this:

'Capture this macro workbook
Dim mcrWB as Workbook
Set mcrWB = ThisWorkbook

Then, throughout your code, you can always return to this workbook like this:

mcrWB.Activate

 

[Chetter]

I like that. I think my problem is that when the macro in the xlsm file is done running it closes itself without saving so it doesn't go back to the workbook trying to open the other workbooks. Here's an example of what I was trying:

Right now it opens 2099.xlsm, runs the macro and stops the sub. How do I get it to open the 2099.xlsm, run the macro in that file (which closes itself when done), then open the 2125 file, run the macro in that file which closes itself when done and repeat through the rest of the files. 8 in total. Thank you!!

' opens BOOK123 2099.xlsm and runs the macro in that workbook then closes it
Workbooks.Open Filename:=ThisWorkbook.Path & "\BOOK123 USE THIS ONE 2099.xlsm"
Application.Run ("'BOOK123 USE THIS ONE 2099.xlsm'!CopyPasteSave2099")


' opens BOOK123 2125.xlsm and runs the macro in that workbook then closes it
Workbooks.Open Filename:=ThisWorkbook.Path & "\BOOK123 USE THIS ONE 2125.xlsm"
Application.Run ("'BOOK123 USE THIS ONE 2125.xlsm'!CopyPasteSave2125")


' opens BOOK123 2160.xlsm and runs the macro in that workbook then closes it
Workbooks.Open Filename:=ThisWorkbook.Path & "\BOOK123 USE THIS ONE 2160.xlsm"
Application.Run ("'BOOK123 USE THIS ONE 2160.xlsm'!CopyPasteSave2160")


' opens BOOK123 2240.xlsm and runs the macro in that workbook then closes it
Workbooks.Open Filename:=ThisWorkbook.Path & "\BOOK123 USE THIS ONE 2240.xlsm"
Application.Run ("'BOOK123 USE THIS ONE 2240.xlsm'!CopyPasteSave2240")

 

[Joe4]

The first thing you need/want to do is to create a loop which opens those 8 xlsm files. The link I provided you previously should show you how to do that.

I would then recommend making the macros in each of the 8 xlsm files all have the same name (i.e. "CopyPasteSave" instead of "CopyPasteSave2125", "CopyPasteSave2160", "CopyPasteSave2240").
Then you can easily call each macro within a loop without having an explicit call for each individual file, i.e.

Dim mcrName as String
mcrName = "'" & ActiveWork.Name & "'" & "!CopyPasteSave"
Application.Run (mcrName)

So, as you can see you are dynamically build the macro name based on the name of the Active Workbook (the "xlsm" file you just opened), and then calling the macro.

 

[Chetter]

I'm getting close. I made some tweaks and got it to go through each xlsm file but at the end it tries to find a macro called "CopyPasteSave" in this workbook, and that macro doesn't exist in this workbook. Also, how can I set the file path to be where this workbook is instead of hard coding it in? Thank you.

Sub UJXlsmFiles()

    Dim strPath As String
    Dim strFile As String
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim mcrName As String
  
   '============================
   'EDIT THE PATH TO THE FOLDER
   '============================
    strPath = "C:\Users\Documents\Weekly Pricing Macro"
'    strPath = ThisWorkbook.Path
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
  
   'get the first file
    strFile = Dir(strPath & "*.xlsm")
    
   'loop through the excel files in the folder
    Do While strFile <> ""

        If Right(strFile, 7) = "BOOK123" Then
'        If InStr(strFile, "USE THIS ONE") > 0 Then
        
                'open the workbook
                Set wbSource = Workbooks.Open(strPath & strFile)
        
                'close the opened workbook
'                wbSource.Close SaveChanges:=False
            End If
'      End If

mcrName = "'" & ActiveWorkbook.Name & "'" & "!CopyPasteSave"
Application.Run (mcrName)
      
      'get the next file
      strFile = Dir()
      
      wbSource.Close savechanges:=False
      
    Loop
  
   'tidy up
    Set wsSource = Nothing
    Set wbSource = Nothing
      
End Sub

 

[Joe4]

This part needs to be moved up above the "End If" line:
Try changing "ActiveWorkbook.Name" to "wbSource.Name", and move it all up above the "End If", i.e.

        If Right(strFile, 7) = "BOOK123" Then

                'open the workbook
                Set wbSource = Workbooks.Open(strPath & strFile)
      
                mcrName = "'" & wbSource.Name & "'" & "!CopyPasteSave"
                Application.Run (mcrName)

                'close the opened workbook
                wbSource.Close SaveChanges:=False
            End If

 

[Chetter]

I must be missing something, I made the changes but now nothing runs:

        If Right(strFile, 7) = "BOOK123" Then
'        If InStr(strFile, "USE THIS ONE") > 0 Then
        
                'open the workbook
                Set wbSource = Workbooks.Open(strPath & strFile)
                
                mcrName = "'" & ActiveWorkbook.Name & "'" & "!CopyPasteSave"
                Application.Run (mcrName)

        
                'close the opened workbook
'                wbSource.Close SaveChanges:=False
            End If

 

[Chetter]

My bad......I did make this change and still doesn't run.

mcrName = "'" & wbSource.Name & "'" & "!CopyPasteSave"