Run Macro in another excel, but fail to return to current sub after the called Macro completed-CodePudding

ParentExcel.xlsm has sub to call a sub in ChildExcel1.xlsm and ChildExcel2.xlsm

//in ParentExcel.xlsm    
Sub ParentSub()

    Set runWorkbook = Workbooks.Open(Filename:=mChildPath)
    Application.Run runWorkbookName & "!" & ChildSub1

    /*Edit*/Set runWorkbook2 = Workbooks.Open(Filename:=mChildPath2)
    Application.Run runWorkbookName2 & "!" & ChildSub2
End sub

//in ChildExcel1.xlsm
Sub ChildSub1()

     MsgBox "Called ChildSub1"
     /*Added*/ThisWorkbook.Close

End sub

//in ChildExcel2.xlsm
Sub ChildSub2()

     MsgBox "Called ChildSub2"
     /*Added*/ThisWorkbook.Close

End sub

My problem is, the procedure stopped after completion of ChildSub1() and the ChildExcel1.xlsm is closed. I then cannot execute the remaining code in the ParentSub()

CodePudding user response：

If you close the Workbook where the code is in (runWorkbook), the execution stops (VBA is single threaded and with the close statement in ChildSub1 the runtime assumes you're done).

You will need to put the close statement into the calling routine - which is cleaner anyhow as with this, the routine that opens the workbook also closes it.

Note that you use runWorkbookName which is neiter defined nor set in the code you show us. You could use the following code instead:

Set runWorkbook = Workbooks.Open(Filename:=mChildPath)
Application.Run runWorkbook.Name & "!" & ChildSub1
runWorkbook.Close 

Set runWorkbook2 = Workbooks.Open(Filename:=mChildPath2)
Application.Run runWorkboo2.kName & "!" & ChildSub2
runWorkbook2.Close

CodePudding user response：

In order to call subs from closed workbooks, please use the next way. Using their full name will make the call opening the necessary workbook. If they will be already open, the code will work, too:

Sub ParentSub()
  dim mChildPath as string, mChildPath2 as string
  mChildPath = "full name of the workbook"         'please fill here the real path
  mChildPath2 = "full name of the second workbook" 'the same as above
    Application.Run "'" & mChildPath & "'!" & "ChildSub1"

    Application.Run "'" & mChildPath2 & "'!" & ChildSub2
End sub

Now, making the calls, the necessary workbooks will be open and they will be closed by their called Sub...