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]

Try adding this line after that one and see what it returns:

MsgBox mcrName

and verify it matches exactly the previous manual line that worked successfully.

 

[Chetter]

Nothing happens at all, it doesn't run any of the code

 

[Joe4]

OK, I think I see the issue.

I think this line:

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

should be:

If Right(strFile, 12) = "BOOK123.xlsm" Then

since it has the extension on the end of it!

 

[Chetter]

Here's what I have but nothing runs, weird. It was running all the way to the end before, when it was looking for a macro that doesn't exist in this workbook.

Option Explicit
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\R616026\OneDrive\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, 12) = "BOOK123.xlsm" Then
'        If InStr(strFile, "USE THIS ONE") > 0 Then
        
                'open the workbook
                Set wbSource = Workbooks.Open(strPath & strFile)
        
                mcrName = "'" & wbSource.Name & "'" & "!CopyPasteSave"
                Application.Run (mcrName)
                
                MsgBox mcrName
                                
                '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]

Do you know how to Step into your code to run it one line at a time, line-by-line, using the F8 key?
If you do this, you can see exactly what it is doing every step of the way.
You can see if it is entering loops, satisfying/entering "If" clauses, etc.
Also, if you hover over any variable (such as "strFile), it will show you the exact value of that variable at that point in time.
And, you can see what is happening on the Excel files as they happen.

This is a very valulable debugging technique that programmers use all the time.
I would highly recommend trying this.
Many times, the problems areas will become quite evident when you see this happening as the code runs one line at a time.

 

[Chetter]

I got it working perfectly. I stepped through and figured out this line was the problem:

If Right(strFile, 12) = "BOOK123.xlsm" Then

I changed it to this:

If InStr(strFile, "USE THIS ONE") > 0 Then

Thank you for everything!

 

[Joe4]

You are welcome.
Glad you got it working!