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.

 

[pgc01]

Hi Jon

There may be easier ways but the only way I could do it is to close workbook before opening it again.

The way I do it is to execute a sub in another workbook that closes the first workbook and reopens it in a new instance.

The test I did:

- Opened a new workbook, saved it as Test.xlsb
- In the ThisModule pasted:

Option Explicit
 
Private Sub Workbook_Open()
Dim wb As Workbook, s As String
 
' If it's the only workbook then exits
If Workbooks.Count = 1 Then Exit Sub
 
s = "Public Sub OpenMe()" & vbNewLine
s = s & "Workbooks(""" & ThisWorkbook.Name & """).Close SaveChanges:=False" & vbNewLine
s = s & "With CreateObject(""Excel.Application"")" & vbNewLine
s = s & "    .Visible = True" & vbNewLine
s = s & "    Call .Workbooks.Open(""" & ThisWorkbook.FullName & """)" & vbNewLine
s = s & "End With" & vbNewLine
s = s & "ThisWorkbook.Close SaveChanges:=False" & vbNewLine
s = s & "End Sub"
 
Set wb = Workbooks.Add
wb.VBProject.VBComponents.Add(1).CodeModule.AddFromString s
 
Application.OnTime Now + TimeSerial(0, 0, 1), wb.Name & "!" & "OpenMe"
End Sub

Saved and closed Test.xlsb

Afterwards, I opened and closed Test.xlsb several times and it seems to be working OK.

 

[Jon von der Heyden]

Hi Pedro

This is a very clever approach

Thank you!

 

[Jon von der Heyden]

I have a related question. Pedro's code works a treat but I am also trying to make sure that any other files that the user loads will open into a different instance. I.e. assume this model is already open. I want the insance that it is open in to be exclusive.

I am attempting to allow the user to still create or open a file but that it always moves to a different instance. I don't want to disable shortcuts and controls.

Class module callled 'cm01_Events':

Option Explicit
Public WithEvents app As Application
Private Sub app_NewWorkbook(ByVal wb As Workbook)
    ActiveWorkbook.Close
    Call NewBook
End Sub

Private Sub app_WorkbookOpen(ByVal wb As Workbook)
    
    Dim strFileName As String
    
    strFileName = ActiveWorkbook.FullName
    ActiveWorkbook.Close False
    Call OpenBook(strFileName)
    
End Sub

Standard module called 'sm09_ManageInstances':

Sub NewBook()
    With CreateObject("Excel.Application")
        .Visible = True
        .Workbooks.Add
    End With
End Sub

Sub OpenBook(ByVal strFileName As String)
    With CreateObject("Excel.Application")
        .Visible = True
        On Error Resume Next
            Call .Workbooks.Open(strFileName)
        On Error GoTo 0
    End With
End Sub
 
Sub OpenThisBook()
    Dim wkb As Workbook, str As String
    Dim win As Window
    Dim lngCount As Long
 
    For Each win In Application.Windows
        lngCount = lngCount - win.Visible
    Next win
    
    If lngCount = 1 Then Exit Sub
     
    str = "Public Sub OpenMe()" & vbNewLine
    str = str & "Workbooks(""" & ThisWorkbook.Name & """).Close SaveChanges:=False" & vbNewLine
    str = str & "With CreateObject(""Excel.Application"")" & vbNewLine
    str = str & "    .Visible = True" & vbNewLine
    str = str & "    Call .Workbooks.Open(""" & ThisWorkbook.FullName & """)" & vbNewLine
    str = str & "End With" & vbNewLine
    str = str & "ThisWorkbook.Close SaveChanges:=False" & vbNewLine
    str = str & "End Sub"
     
    Set wkb = Workbooks.Add
    wkb.VBProject.VBComponents.Add(1).CodeModule.AddFromString str
     
    Application.OnTime Now + TimeSerial(0, 0, 1), wkb.Name & "!" & "OpenMe"
    
    Set wkb = Nothing
    
End Sub

Standard module called 'sm10_Events':

Option Explicit
Public myAppEvents As New cm01_Events
Sub TrapApplicationEvents()
    Set myAppEvents.app = Application
End Sub
Sub CancelApplicationEvents()
    Set myAppEvents.app = Nothing
End Sub

ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    Application.IgnoreRemoteRequests = True
    Call OpenThisBook
    Application.OnTime Now + TimeSerial(0, 0, 1), "TrapApplicationEvents"
    shtHome.Range("rngCurrentUser") = Replace$(Environ$("username"), ".", " ")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.IgnoreRemoteRequests = False
    Call CancelApplicationEvents
End Sub

NewBook only triggers with File > New but the shortcut CONTROL+N does not work. Do you have any idea why? I tried to change CONTROL+N using the OnKey method to create a blank book in a new instance but it doesn't seem to do a thing.

Also, I would prefer not to create a new instance each time. Can anybody suggest how I make sure that workbooks are opened / created in an already available instance if I have already created a new one?

Thanks

 

[pgc01]

... how I make sure that workbooks are opened / created in an already available instance if I have already created a new one?

Jon

Did you try to store the application reference from when you created it?

I tested this and all files are opened in the same instance:

Option Explicit
 
Sub test()
OpenSameInst "c:\tmp\x.xls"
OpenSameInst "c:\tmp\y.xls"
OpenSameInst "c:\tmp\z.xls"
End Sub
 
Sub OpenSameInst(sPathname As String)
Static MyApp As Excel.Application
    
    If MyApp Is Nothing Then
        Set MyApp = CreateObject("Excel.Application")
        MyApp.Visible = True
    End If
    
    MyApp.Workbooks.Open sPathname
 End Sub

 

[Jon von der Heyden]

That's it! Thanks again Pedro!

Any idea why CONTROL+N to create a new book is different to File > New > Blank? My application event to trap a new workbook doesn't fire when using CONTROL+N.

 

[pgc01]

Jon, I'm glad it helped.

This is an interensting thread. I'm curious, why is it so important that this instance only has that specific file?

Any idea why CONTROL+N to create a new book is different to File > New > Blank? My application event to trap a new workbook doesn't fire when using CONTROL+N.

Unfortunately I cannot help you there.

I tested in excel 2007 and excel 2000 (I don't have xl2003), in both cases with 4 options

- File->New
- "File New" button from the Quick Access Toolbar (07) / Standard Toolbar (00)
- Control-N
- vba statement

workbooks.Add

In all cases in both versions the event was always trapped.

It is as if you have some macro assigned to Control-N.

I cannot reproduce your problem.

 

[pgc01]

P. S.

You could try to reset Control-n

Application.OnKey "^n"

 

[Jon von der Heyden]

Hi Pedro

It was user error, I assigned CONTROL+N to nothing, i.e:
Application.OnKey "^n",""

This is an interensting thread. I'm curious, why is it so important that this instance only has that specific file?

I'm effectively building a dictator application. My client needs a small scale bespoke stock management tool. There are multiple users so to avoid workbook sharing issues the tool will read and write data to mdb tables (using ADO), but I am using Excel for the front-end. Although the tool only ever opens in read-only, they can still write data to the tables and produce reports. I'm using charts and pivot tables quite extensively and I find it easier to build and support in Excel rather than Access. I have finally gone down the route of disabling toolbars and the worksheet menu and created new toolbar and menu to only allow specific controls.

So this code will now ensure that the users can only work on other files in a seperate instance, giving the impression that this tool is an entirely stand-alone app.

Thank you so much for all of your help. Greatly appreciated!

 

[SteveInAlabama]

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.