force file to open in dedicated instance

[Jon von der Heyden]

Hi All

I'm trying to force a particular file to open in its own dedicated instance of the Excel application. The macro below checks how many other files are open (by counting visible windows). If no other files are open then no problem. However, if there are other files open then it needs to re-open in a new instance and then close ThisWorkbook. This bit is ok but (naturally) the file opens in read only.

Option Explicit
Sub auto_open()
    Dim win As Window
    Dim lngCount As Long
 
    For Each win In Application.Windows
        lngCount = lngCount - win.Visible
    Next win
 
    If lngCount = 1 Then GoTo Finish
 
    With CreateObject("Excel.Application")
        .Visible = True
        '.DisplayAlerts = False
        Call .Workbooks.Open(ThisWorkbook.FullName)
    End With
 
    ThisWorkbook.Close False 'this should still trigger the before_close routine
    Exit Sub 'don't actually need this
 
Finish:
    '//call User Interface routine here
    ThisWorkbook.SaveAs ThisWorkbook.FullName 'won't trigger if file is opened through this routine
End Sub

Does anybody have an idea how to make the file open in write mode. I tried adding a SaveAs statement to save over itself but it won't allow it. The auto_open macro isn't triggered when the file is reopened with this macro.

 

[Jaafar Tribak]

Jon,
I have a situation almost identical to yours, which I just posted separately. How did you solve this? When I tested your code, it did not force a dedicated instance of Excel.

Check my post in this thread on how to ensure a workbook is always open in a seperate instance. I hope it achieves what you want.