How to open Excel in new instance?

[dmadhup]

Hello,

I am using Office 365 and when I open two Excel file then it opens on the same instance. For some reason, I am looking to open in a separate instance.
Anyone has an idea how to achieve it. I appreciate your response.

Thank you!

 

[Momentman]

You can hold the ALT button while you click the Excel icon to open, so hold the ALT till it asks if you want to open in a new instance. Thats one way

 

[dmadhup]

Hi Momentman,

Thank you for the response.
ALT option is just okay for me but not all team members. That's why I am looking for an alternative solution.
Maybe there should be some VBA code I can put inside the file and when you open the file the script allows it to open a new instance.

 

[Jaafar Tribak]

You could use some small vba code as follows in the Workbook_Open event :

Option Explicit

Private Sub Workbook_Open()
    Dim oExcel As Excel.Application
    
    If Application.Workbooks.Count > 1 Then
        With Me
            Set oExcel = New Excel.Application
            .Saved = True
            .ChangeFileAccess xlReadOnly
            oExcel.Workbooks.Open .FullName
            oExcel.Visible = True
            .Close False
        End With
    End If
End Sub

Note that the above code will load the workbook in a seperate excel instance as requested and is cleaner but it will not load any addins or excel startup workbooks that you maybe using . If that is an issue, you will have to use the Shell command .. Something like this :

Option Explicit

Private Sub Workbook_Open()
    If Application.Workbooks.Count > 1 Then
        With Me
            .Saved = True
            .ChangeFileAccess xlReadOnly
            Shell "excel.exe " & .FullName
            .Close False
        End With
    End If
End Sub

 

[dmadhup]

Thank you, Jaffar for posting solution.

The 1st code works for me but when I open any other workbook which has not the code inside then again it merges the instance. In Workbook_Open() sub I am only calling userForm. Now the problem I am having is, after inserting your the code, the userform don't pop up and throwing the error message "Microsoft Excel is waiting for another application to complete an OLE action"

 

[Jaafar Tribak]

Sorry - What do you mean by "it merges the instance" ?

Also, if you could elaborate more about the userform and so on .

Regards

 

[dmadhup]

merge instance:
I mean to say they open on the same instance. If I go to VIEW --> switch windows then able to see all open workbooks. Actually, I don't want to see all the workbooks there.

I am using the following code:

Private Sub Workbook_Open()
Windows(WorkbookName()).Activate


    Dim oExcel As Excel.Application
    
    If Application.Workbooks.Count > 1 Then
        With Me
            Set oExcel = New Excel.Application
            .Saved = True
            .ChangeFileAccess xlReadOnly
            oExcel.Workbooks.Open .FullName
            oExcel.Visible = True
            .Close False
        End With
    End If






Call LoginForm.Show




End Sub

 

[Jaafar Tribak]

Try this :

Option Explicit

Private Sub Workbook_Open()

    Dim oExcel As Excel.Application
    
    If Application.Workbooks.Count > 1 Then
        Set oExcel = New Excel.Application
        With oExcel
            Me.Saved = True
            Me.ChangeFileAccess xlReadOnly
            .IgnoreRemoteRequests = True
            .Workbooks.Open Me.FullName
            .Visible = True
            .OnTime Now, Me.CodeName & ".ShowUserForm"
            Me.Close False
        End With
    Else
        Call ShowUserForm
    End If
    
End Sub

Private Sub ShowUserForm()
    Call LoginForm.Show
End Sub

Late Edit:
The code may need some tweaking if you have Personal or startup workbooks loaded.

 

[dmadhup]

Still No luck. But I found the main issue. Here it is:

I multiple window open, then LoginForm stays behind all the window, It's not visible in the front so that I can interact with it. However, If I don't use the code you provided then Loginform window is visible as active form and visible.

 

[Jaafar Tribak]

Still No luck. But I found the main issue. Here it is:

I multiple window open, then LoginForm stays behind all the window, It's not visible in the front so that I can interact with it. However, If I don't use the code you provided then Loginform window is visible as active form and visible.

1- When you use the code , does the workbook open in a sepearte instance ?

2- Are you opening the workbook from the File Menu, from explorer or via code ?

3- What edition of excel are you using ? Excel 2007, 2010 ... etc ?

If you could answer the above so we can see if we can take this further.