Solved: Always open a new instance of excel

[boc_est1986]

Hi,
I'm writing a program in VBA but i want to make sure that every time it opens in open in a new instance of excel and doesnt open with any workbooks that might already be open on the computer. Please can you help

[GTO]

Hi there,

At least to me, this is a more than just a couple of lines. Thinking of things a bit out of order, once we have the wb open in its own instance, we can used the application events to stop other wb's from opening in that instance. First though, if there is an instance in existance and the user opens the wb either through that instance or through windows explorer, then the wb will be open in the existing instance.

If you wished to keep it simple, I would think of using the workbook open event. If other workbooks are open, then tell the user and close the wb w/o saving.

If you want the wb to 'fix' this on its own, here's what I have tried.

Maybe someone will show a better way. As I recall, it worked okay, but I could not figure out how to identify specific instances of the app, which seemed like the next step to me. Anyways, see attached.

[boc_est1986]

thanks very much, works brilliatly

[boc_est1986]

ok didnt quite work perfectly. When i open everything the form you created stays on the screen and the other workbook doesnt open until i close the other form that i am trying to activate has been close. ive attached my code for you to have a look at

[GTO]

That is a sizeable project that I am afraid I do not have time to thoroughly learn/debug.

In ThisWorkbook try commenting out as below:

Private Sub Workbook_Open()
    Set clsAPP.XLAPP_ORIG = Application
    
    If Application.UserControl Then
        If Application.Workbooks.Count > 1 Then
            Application.Visible = False
            DoEvents
            frmCreateReplicant.Show vbModal
            Exit Sub
        End If
    End If
    
'    Do
'        DoEvents
'    Loop While frmCreateReplicant.Visible
    
    Call ThisWorkbook_CompleteOpening
End Sub

Off the top and not thought through, I would suggest adding a delay to showing your form in order to give the initial form time to be gone. I believe if you look through the code, the initial form only goes away after the wb has 'replicated' and reopened in the new instance (if one is needed).

Hope that helps,

Mark

[boc_est1986]

Thanks for the reply