Workbook_Open not working when I open using VBA

[baggieade]

Hi

I have a spreadsheet that takes along time to run and I need to perform several runs of it. I want to run the tests in multiple instances of Excel so that they can run at the same time.

I have got the code working to open new instances of Excel, open the copy of the spreadsheet andcall the macro I want calling. However when doing this it won’t open the nextworkbook until the macro has finished so doesn’t save any time.

I decided to add a Workbook_Open to call the macro insteadwithin the workbook I am opening but this isn’t activated when I open theworkbook using code but works if I open it manually.

Is there a way around this so the workbook can be opened,the macro called and another workbook being opened before the macro hasfinished running in the 1^st one?

 

[RoryA]

Are you disabling events in the code?

 

[offthelip]

You could get your macro to make a copy of the workbook first befroe it opens it, that might solve the problem

 

[baggieade]

I'm not disabling events in the code (alerts are disabled though).

The workbooks are presaved with the inputs stored. Would making a copy of the workbook make something different happen to just opening it?

 

[offthelip]

Would making a copy of the workbook make something different happen to just opening it?

Yes it might, when you open a workbook it defaults to opening it with read and write access. Depending on your version of EXCEL it may or may not allow other people ( or another instance of excel) to also open it with read write access, my version (2007) doesn't permit it, some later ones do

 

[RoryA]

Is it definitely a workbook_open event and not an auto_open routine?

 

[baggieade]

Is it definitely a workbook_open event and not an auto_open routine?

Thanks for the tip. It was workbook_open but not within the Private Module for the ThisWorkbook. The macro is now called automatically on opening which is what I wanted. However it hasn't solved my overall problem because it won't open the next workbook until the macro in the 1st one has finished.

This is my loop:-
' set the current instance of excel
Set obj_Excel_Current = GetObject(, "excel.application")
'Loop through all of the test cases contained within the test cases array and open the saved C0 and run the main routine
For Each int_Current_TC In arr_Test_Cases
' open a new instance of Excel
Set obj_Excel_New = CreateObject("excel.application")
obj_Excel_New.Visible = True
str_File_Location = str_C0_Save_Location & "C0_" & int_Current_TC & ".xlsm"
obj_Excel_New.Workbooks.Open (str_File_Location)
Next int_Current_TC

It is supposed loop through an array of test cases opening a new instance of excel each time, start the macro and then open the next test case straight away and not wait for the macro in the previous one to finish. I can make this happening manually but I can't get this to work in code even though I'm working with difference instances of excel.

 

[RoryA]

Put the code back in a normal module, and add a routine that calls it using OnTime (use a time delay of 1 second). You can then run that routine from your main code and control will return almost immediately, while leaving the other instance to run the scheduled code.

 

[baggieade]

Put the code back in a normal module, and add a routine that calls it using OnTime (use a time delay of 1 second). You can then run that routine from your main code and control will return almost immediately, while leaving the other instance to run the scheduled code.

Thanks. The Ontime delay was the solution. It gave enough time for the spreadsheet to call on and open the next instance!