Using a Macro within a file to open and run another Macro Enabled Workbook

[DBCox]

Hi everyone,

With a lot of help from you guys, I have developed a macro enabled workbook that handles updating inventory on my website. However, it got to the point it was so big and complex, it was taking too long to run, was extremely difficult to modify (due to all of the references and the time it takes to update all calcs), and was even unstable at times. I noticed I was getting odd results, and read where Excel can become unstable when the file size exceeds 50mb. This one exceeds 70...

So, I broke it down into multiple workbooks. So far, I only have 2 workbooks, but intend to add at least one more, if not 2 or 3 more. The idea is to open my primary "UpdateWorkbook.xslm" with windows task manager. From there, the auto run macro will perform some tasks (download updated inventory data), then run my individual workbooks (each with an autorun macro), then upload the updated information.

The uploading and downloading data is working fine. The individual workbooks that I want to automate the opening/running of are working as expected too. Opening the other macro enabled workbooks seems to be the problem. I had a similar code developed for saving as a file type, so I tried to use that code. That code is below. The beauty of it is it looks at columns on a sheet in the workbook to determine what files to open. So, as I add more workbooks in the future, I can just include their name on that sheet and we are good to go without having to hard code anything else.

Any ideas who I can make the secondary macro enabled workbooks open with code, run, close, and then move on to the next one on the list?

counter = 2 'initialize variable
Sheets("Files to Run").Select
Range("a2").Select '1st cell with file name
Do Until ActiveCell = "" 'do until condition
    fname1 = Cells(counter, 1) 'selects cell with file name
    'this is set for column A
    filext = Cells(counter, 2) 'define file extension
    fnamesuffix = Cells(counter, 3) 'define file extension
    FileFormatVar = Cells(counter, 4) 'define file extension
    fname = directory & fname1 & fileext 'file location
    Workbooks.Open Filename:=fname 'open the xls file
    
    ActiveWorkbook.Close SaveChanges:=False 'close WB

    Windows("UpdateWorkbook.xlsm").Activate 'select workbook with file info
    Sheets("Files to Run").Select 'select sheet with file info
    counter = counter + 1
    ActiveCell.Offset(1, 0).Range("a1").Select 'This moves down the column

 

[pplstuff]

Here's something to get you started. You'll have to put the loop around this so it runs through all your workbooks. This will open a workbook, run a macro from that book, save changes and close the workbook.

Sub test()
    
    Dim sFileName As String
    Dim wbTemp As Workbook
    Dim sMacroName As String
    
    sFileName = ""
    sMacroName = ""
    
    'Set variable, open workbook
    Set wbTemp = Workbooks.Open(sFileName)
    
    'Run macro from other workbook
    Application.Run wbTemp & "!" & sMacroName
    
    'Close workbook, save changes
    wbTemp.Close True
    
End Sub

 

[JLGWhiz]

There us a couple of ways to do what you describe. The simplest is probably to use a Workbooks_Open event in the workbooks where you want to run the code, the use a control workbook with a single macro to open each workbook as needed. For example, Workbook A will be the control workbook and Workbooks B and C will be the ones with the data and the macros that manipulate the data.
Workbook A

Sub macrocontrol()
 On Error Resume Next
 Set wb1 = Worbooks("B.xlsm") 
 If wb1 Is Nothing Then 'Checks if wb1 is open
  Set wb2 = Workbooks("C.xlsm")
  If wb2 Is Nothing Then 'Check if wb2 is open
   Workbooks.Open wb1
  Else
   wb1.Close True
   Workbooks.Open wb2
  End If
 End If
 On Errer GoTo 0 
End Sub

Woorkbok B abd C would contain identical code in the ThisWorkbook code module.

Private Sub Workbooks_Open()
 Run myMacro 'This would be the macro that does your work
 ThisWorkbook.Close True 'Close and save changes
End Sub

 

[DBCox]

JLGWhiz,

Thank you for the example code and I apologize for taking a couple of days to get back to you. I am not a programmer at all, but I am having trouble following the code. Perhaps I am misunderstanding it. It appears as if it will use Workbook A to control the others (which is good), and then open Workbook B if it is not already open. If it is already open, then it will open Workbook C, is that correct?

Do I need to add those lines of code for every workbook I need to open? I cannot tell how it is cycling through to insure everything is opened and closed.

Ideally, I could change it slightly to read a list of filenames in a Worsheet of Workbook A. That way, as I add workbooks in the future, I can just add the names in the next open cell, rather than changing code.

Thanks!

 

[DBCox]

Ok, I've been working with this a little more. I went back to my original code and found an error. I corrected it, and the code is opening the secondary workbooks like I want it to, but, the auto-run macros in those workbooks are not running. My assumption is because I am already running a macro in my primary/control workbook. If the auto run macros in the secondary workbooks will not automatically start, is there a way change them to a non-auto run macro and call them to run from the control workbook code?

The code I am currently running is:

Do Until ActiveCell = "" 'do until condition
    fname1 = Cells(counter, 1) 'selects cell with file name
    'this is set for column A
    fileext = Cells(counter, 2) 'define file extension
    fname = directory & fname1 & "." & fileext 'file location
    Workbooks.Open fname 'open the xls file
    
    ActiveWorkbook.Close SaveChanges:=False 'close WB

    Windows("UpdateWorkbook.xlsm").Activate 'select workbook with file info
    Sheets("Files to Run").Select 'select sheet with file info
    counter = counter + 1
    ActiveCell.Offset(1, 0).Range("a1").Select 'This moves down the column
    
    
Loop

 

[DBCox]

Got It!


pplstuff, I apologize, but I did not see your response earlier. I was able to combine what I had, with both of your responses, and now she's working great!

Thanks!