Results 1 to 6 of 6

Thread: How to make VBA save, close and re-open a workbook as a new separate Excel instance?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    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 by Sam Hamels; Jun 3rd, 2019 at 12:08 PM.

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,806
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

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

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

    Google saving and closing workbook by name

  3. #3
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Logit View Post
    .
    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 by Sam Hamels; Jun 3rd, 2019 at 03:24 PM.

  4. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

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

    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 by Jaafar Tribak; Jun 4th, 2019 at 06:41 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #5
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Thank you very much

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

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

    Quote Originally Posted by Sam Hamels View Post
    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.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •