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

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
47
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

Code:
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. :confused:

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
 
Last edited:

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
Try naming the specific workbook/s instead of using "ThisWorkbook".

Google saving and closing workbook by name
 

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
47
.
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.
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
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)
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
I have no idea WHY that works, but it WORKS!

Thank you very much :biggrin:
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.
 

Forum statistics

Threads
1,082,309
Messages
5,364,420
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top