PDA

View Full Version : Solved: Always open a new instance of excel



boc_est1986
05-26-2010, 12:06 AM
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
05-26-2010, 01:43 AM
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
05-26-2010, 05:58 AM
thanks very much, works brilliatly

boc_est1986
05-26-2010, 06:19 AM
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
05-26-2010, 07:05 AM
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
05-26-2010, 07:06 AM
Thanks for the reply