Open New Instance

[Av8tordude]

The first code closes any new workbook and the second code opens a new instance. If an attempt to open an existing file or start a new workbook, Instead of closing the new workbook, I would like it to open in a new instance. How can I combine them?

If Workbooks.Count > 1 Then
    For Each wbk In Application.Workbooks
        If (Not wbk Is Application.ThisWorkbook) Then
            MsgBox "Opening additional workbook in the same instance is restricted while using Traderking - Trade Journal.", vbCritical, "Traderking - Trade Journal"
            wbk.Close SaveChanges:=False
        End If
    Next
End If

Sub New_Instance_of_Excel()
    Dim objXL
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    objXL.Workbooks.Add
End Sub

 

[KOKOSEK]

Private Sub Workbook_Open()
If Application.Workbooks.Count > 1 Then
Call Shell(Application.Path & Application.PathSeparator & "excel.exe " & Chr(34) & ThisWorkbook.FullName & Chr(34))
ThisWorkbook.Close SaveChanges:=False
End If
end Sub

 

[Av8tordude]

Thank you for the quick response.

It appears this

Private Sub Workbook_Open()
If Application.Workbooks.Count > 1 Then
Call Shell(Application.Path & Application.PathSeparator & "excel.exe " & Chr(34) & ThisWorkbook.FullName & Chr(34))
ThisWorkbook.Close SaveChanges:=False
End If
end Sub

The code you provided is an open event procedure, but I would like this for the workbook deactivate event. I did place it in the deactivate event, but it says it can not find the workbook if I'm just starting a blank workbook (a non-existent file).

If Workbooks.Count > 1 Then
    For Each wbk In Application.Workbooks
        If (Not wbk Is Application.ThisWorkbook) Then
            Call Shell(Application.Path & Application.PathSeparator & "excel.exe " & Chr(34) & ActiveWorkbook.FullName & Chr(34))
        End If
    Next
End If

 

[KOKOSEK]

Once more.
In case if user wants to open second workbook or create new workbook, this possibility have to be block and new excel instance have to be open?

 

[Av8tordude]

WIth my current workbook open,

1. if the user navigates to a exiting file, that existing file should open in its own instance.
2. if the user opens a new worksheet/workbook, this new worksheet/workbook should open in its own instance.

 

[Marc L]

​

Processing like this I hope the Excel computer has at least 16 Go RAM, even more !​

 

[KOKOSEK]

Private Sub Workbook_Deactivate()
Dim tmpName As String
For Each wbk In Application.Workbooks
        If (Not wbk Is Application.ThisWorkbook) Then
            MsgBox "Opening additional workbook in the same instance is restricted while using Traderking - Trade Journal.", vbCritical, "Traderking - Trade Journal"
            tmpName = wbk.FullName
            wbk.Close SaveChanges:=False
            If InStr(1, tmpName, ".xl") Then     ' quick test that new workbook is already saved and open as second or new workbook
                Call Shell(Application.Path & Application.PathSeparator & "excel.exe " & Chr(34) & tmpName & Chr(34))   ' reopen existed in new instance
            Else
                Call Shell("excel.exe")    'create new instance
            End If
        End If
Next
End Sub

 

[Av8tordude]

Hi Kokosek,

As the code runs through, it closes the new workbook, but it reopens the workbook in the same instance again. Also, it does this continuously. I have to ctrl-break the code to stop it from running.

 

[KOKOSEK]

I do not know what version of excel you are using. On 365 works fine (small tweak):

Private Sub Workbook_Deactivate()
Dim tmpName As String
For Each wbk In Application.Workbooks
        If (Not wbk Is Application.ThisWorkbook) Then
            MsgBox "You can't open other workbook in the same instance. New instance will be created.", vbCritical, "Second workbook open"
            tmpName = wbk.FullName
            wbk.Close SaveChanges:=False
            If InStr(1, tmpName, ".xl") Then     ' quick test that new workbook is already saved and open as second or new workbook
                Call Shell(Application.Path & Application.PathSeparator & "excel.exe " & Chr(34) & tmpName & Chr(34))   ' reopen existed in new instance
            Else
                Call Shell(Application.Path & Application.PathSeparator & "excel.exe ")    'create new instance
            End If
        End If
Next
End Sub

If I press Ctrl+N (or choose New from File menu) I've got message, new workbook is closed and new instance of excel is created with new workbook.
The same if I open existed workbook.

 

[Av8tordude]

I have Excel 2019. Thank you for the help Kokosek