How to make VBA save, close and re-open a workbook as a new separate Excel instance?

[Sam Hamels]

Hi All,


I use the following code (found here) to do the following:

- Save
- Set up a Windows prompt that will re-open the Excel file in a few seconds
- Close the workbook
- Prompt triggers a re-opening of the workbook

Sub SaveCloseReOpen()
Dim strCMD As String


strCMD = "CMD /C PING 10.0.0.0 -n 1 -w 5000 >NUL & Excel.exe " & Chr(34) & ThisWorkbook.FullName & Chr(34)


ThisWorkbook.Save
Shell strCMD, vbNormalFocus
If Application.Workbooks.Count = 1 Then
    Application.Quit
Else
    ThisWorkbook.Close SaveChanges:=False
End If


End Sub

I need to run several separate instances of Excel at the same time, and they each need to use this code.
The separate Excel instances are originally set-up by left-mouse clicking the open Excel program in the bottom toolbar and clicking Excel while continuously holding ALT (you then get a prompt asking if you want to open a new separate Excel instance). This allows me to run macro's in each separate Excel instance simultaneously.

The problem is that, when each of the separate Excel instances executes the code above, they will all be re-opened in a single Excel instance.
My question therefore is: how could I adapt the code above, to make the Windows prompt re-open the Excel file as a new, separate Excel instance.

After being re-opened, each Excel instance (workbook) automatically starts executing a macro, which doesn't work out when they re-open all together as one (non-separated) Excel instance, because the whole instance then becomes 'busy' doing one single macro that started running in whichever instance/workbook that re-opened first.



Cheers,
Sam

 

[Logit]

.
Try naming the specific workbook/s instead of using "ThisWorkbook".

Google saving and closing workbook by name

 

[Sam Hamels]

.
Try naming the specific workbook/s instead of using "ThisWorkbook".

Google saving and closing workbook by name

Each Excel file (workbook) that should be opened and running macro's in computationally separate Excel instances is already given a unique name.

Having the above code running in all workbooks already works in the sense that each unique workbook is saved, closed and re-opened by its own Windows prompt.

The problem is that, after re-opening, I now have one instance of Excel open (in task manager), with each workbook re-opened as part of the same Excel instance.

This means that, as soon as a macro starts running in one of the workbooks, all other workbooks freeze up. In that moment, all open workbooks are part of the same 'single' program 'Excel' that is running.

If, of the other hand, I would succeed in each workbook re-opening as a computationally separate instance of Excel (i.e. with its own separate spot in task manager), then each workbook can run its own macro's simultaneously.

 

[Jaafar Tribak]

Try passing the /x switch to the commandline :

strCMD = "CMD /C PING 10.0.0.0 -n 1 -w 5000 >NUL & Excel.exe " & Chr(34) & ThisWorkbook.FullName & Chr(34) & "/x" & Chr(34)

 

[Sam Hamels]

I have no idea WHY that works, but it WORKS!

Thank you very much

 

[Jaafar Tribak]

I have no idea WHY that works, but it WORKS!

Thank you very much

Passing the /x switch in the commandline string is the same as executing excel.exe /x from the Run command to open a new instance of excel.

Glad it worked for you.