Running code simultaneously across instances

[nightcrawler23]

Hi all,
I have a workbook with macros in it. If I make copies of this workbook and open then in different instances of excel i can run the macro in each instance simultaneously (I have tried this). The same thing i want to accomplish using code as each macro takes at least 5 mins to complete.

The below code runs perfectly fine but not the way i wanted . I had to change the code to open the workbook run the code and close them one by one.

i call this code form a userform. I have tried adding DoEvents in the button code. It doesn't help.

Any ideas on how to do this.

Sub runDP()

'/// some code here///

Dim oXl As Excel.Application
Dim oWB As Excel.Workbooks
Dim sceneWB As Excel.Workbook
Dim sceneBatSheet As Excel.Worksheet

Application.DisplayAlerts = False
For i = 1 To lr - 3
    Set oXl = New Excel.Application
    Set sceneWB = oXl.Workbooks.Open(ThisWorkbook.Path & "\" & batchSheet.Range("B" & (i + 3)) & ".xls")  ' open the workbook
    Call genScene(oXl, sceneWB, i + 3)      ' fill in parameters
    oXl.Run ("myMacro")                          'run the code
    sceneWB.Close (True)      'save and close
    oXl.Quit      'close the instance
    Set oXl = Nothing
Next i
Application.DisplayAlerts = True

Set oXl = Nothing
End Sub

 

[big_red_machine]

I'm guessing the issue is that your code waits for "myMacro" in each workbook to finish before continuing to the next workbook...

I havn't tested it but try replacing

oXl.Run ("myMacro")

with

oXl.OnTime Time + TimeSerial(0,0,1), "myMacro"

I have found that Run is synchronous while OnTime is Asynchronous.

EDIT: Looking back at your code you are likely to run into issues with the above approach because your controlling code will set the macro to run (in 1 second) and then immediately close the excel instance. Therefore I would suggest the approach below - let each workbook run and close itself.

Alternatively you might be able to put the macro you wish to run in the Workbook_Open() of each workbook and then open each workbook in a new excel instance in your controlling code. The code inside each workbook would then have to be responsible for closing the workbook at the end.

 

[nightcrawler23]

HI BRM,
i will not be able to put my code in the workbook_open event as I have three macros and which macro is to be run is decided before opening the workbook.

if I am able to make a public variable and pass it to that instance of excel in order to execute the particular macro, may be then it is possible.

 

[big_red_machine]

OK, scratch the Workbook_Open suggestion - although i think it could work. The easier way is to pursue changing .Run to .OnTime.

Referring to your original example code:

    Set oXl = New Excel.Application
    Set sceneWB = oXl.Workbooks.Open( ...
    Call genScene(oXl, sceneWB, i + 3)
 
    'oXl.Run ("myMacro") ' replace this line with:
    oXl.OnTime Time + TimeSerial(0, 0, 1), "myMacro"
 
    'Remove the following lines:
    'sceneWB.Close (True) ' changes will need to be saved at the end of "myMacro"
    'oXl.Quit ' call Application.Quit instead at the end of "myMacro"
    'Set oXl = Nothing

There might be a cleaner way to call "myMacro" asynchronously but not that I'm aware of.

 

[nightcrawler23]

Thanks BRM, This works perfect.

Now i just have to figure out how to check if all the excel instance that were happened have closed.

Thanks a lot.

 

[nightcrawler23]

Apologies.

When i run the code with the xl instances visible, i see the code still run one by one for each instance.

 

[nightcrawler23]

Apologies again,
please ignore previous post. The code runs one by one only in debug mode. It works fine if run normally.