Open file in own instance of Excel application

Jon von der Heyden

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,912
Office Version
  1. 365
Platform
  1. Windows
Hi

I have asked similar question to this before but it involved programming the VBE (VB extensibility) and a method I am attempting to avoid.

I have a workbook that I would like to make sure it opens in it's own instance of the application, and that any subsequent files opened are not opened in this instance.

I am using a separate workbook (loader) to call the workbook that I want open in its' own dedicated instance.

This code is in the loader:
Code: 
Private Sub Workbook_Open()
    Dim app As Excel.Application
    Dim strUIPath As String
    Dim wkbUIApp As Workbook
    Dim blnErr As Boolean
    
    Set app = New Excel.Application
    strUIPath = Environ$("ProgramFiles") & "\Recruitment Cost Tracker\RCT_App.xls"
    
    With app
        .IgnoreRemoteRequests = True [COLOR=DarkRed]'So that subsequent files do not interfere with this instance[/COLOR]
        .Visible = False
        .DisplayAlerts = False
    End With
    
    On Error Resume Next
        Set wkbUIApp = app.Workbooks.Open(Filename:=strUIPath)
        If wkbUIApp Is Nothing Then
            blnErr = True
        End If
    On Error GoTo 0
    
    If blnErr Then
        MsgBox Prompt:="The application failed to load!", _
               Buttons:=vbCritical + vbOKOnly, _
               Title:="Recruitment Cost Tracker"
    End If
    
    If Application.Workbooks.Count > 1 Then
        ThisWorkbook.Close SaveChanges:=False
    Else
        With Application
            .IgnoreRemoteRequests = False
            .Quit
        End With
    End If
    
End Sub

The idea is that the loader can be called from the windows start menu or from desktop. It should point to the workbook, open it in it's own instance, and the the loader should close. If the loader was opened in a new instance then that instance should close. If the loader was opened in an existing instance with other workbooks already open then only the loader workbook should be closed and the instance remain.

Problem:
This doesn't appear to be loading the workbook in it's own instance.
The IgnoreRemoteRequests is not resetting to False, hence trying to open any other workbook when the entire app is closed doesn't work if I attempt to open it from windows explorer (instead of Excel). I get error msg 'There was a problem sending te command to the program'.

Any ideas on how to achieve much appreciated. Thanks :)
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
i'm not sure i get it all but why not use shell to open a new instance of excel - completely independent.
shell "excel.exe "&""""&"c:\Documents and Settings\....\....xls"&"""",vbNormalFocus
 
Upvote 0
Argh why didn't I think of that?? Brilliant suggestion! :beerchug:
I have updated as follows:
Code: 
Private Sub Workbook_Open()
    Dim strUIPath As String
    Dim dblTaskID As Double
    
    strUIPath = Environ$("ProgramFiles") & "\Recruitment Cost Tracker\RCT_App.xls"
    
    dblTaskID = Shell(PathName:="excel.exe " & """" & strUIPath & """", WindowStyle:=vbNormalFocus)
    
    If dblTaskID = 0 Then
        MsgBox Prompt:="The application failed to load!", _
               Buttons:=vbCritical + vbOKOnly, _
               Title:="Recruitment Cost Tracker"
    End If
    
    If Application.Workbooks.Count > 1 Then
        ThisWorkbook.Close SaveChanges:=False
    Else
        With Application
            .Quit
    End If
End Sub

And then I moved the rest to the open event of the UI workbook (i.e. ignore remote requests, app invisible etc...)

Thanks for getting me back on track bobsan42 :)
 
Upvote 0
Why not just use CreateObject, and open a document using that object variable?
 
Upvote 0
glad i could help
:beerchug: cheers and good luck
 
Upvote 0
xld said:
Why not just use CreateObject, and open a document using that object variable?
Click to expand...

@xld

I'll have a toy with that too, as perhaps I can keep all control of the application in the loader then and mitigate the open event in the UI workbook.

Ok I know I need to test it but isn't using CreateObject *roughly* the same as what I was doing, only that CreateObject is a late bound method opposed to early bound? And therefore should I anticipate the same behaviour as before?
 
Upvote 0
Yes it is. Your opened workbook (not the controlling one) needs to reset the IgnoreRemoteRequests before it closes, otherwise that value gets written to the registry last.
 
Upvote 0
rorya said:
Yes it is. Your opened workbook (not the controlling one) needs to reset the IgnoreRemoteRequests before it closes, otherwise that value gets written to the registry last.
Click to expand...

It did reset it... The app is closed using a command button control on a userform, and the code in its' click event explicitly tells it to reset IgnoreRemoteRequests before it closes the application.

But it doesn't either seem as though that initial method I was using was actually creating a dedicated instance.
 
Upvote 0
Jon.

If you put the following code directly tin the target workbook module then the target workbook should always be opened alone in its own Excel instance whether you open it from the File>Open menu, from the Windows Shell or even via code.

Moreover, you will have no need to use an intermediate workbook loader.

Code: 
Private WithEvents oAppEvents As Application
 
Private oWb As Workbook
 
Private Sub Workbook_Open()
 
    Dim oNewApp As New Application
 
    If Application.Workbooks.Count > 1 Then
        Me.ChangeFileAccess xlReadOnly
        oNewApp.Workbooks.Open Me.FullName
        oNewApp.Visible = True
        Me.Close False
    End If
 
    Set oAppEvents = Application
 
End Sub
 
Private Sub oAppEvents_NewWorkbook(ByVal Wb As Workbook)
 
    Dim oNewApp As New Application
 
    Wb.Close False
    oNewApp.Workbooks.Add
    oNewApp.Visible = True
 
End Sub
 
Private Sub oAppEvents_WorkbookOpen(ByVal Wb As Workbook)
 
    If Wb Is Me Then Exit Sub
    Set oWb = Wb
    oWb.ChangeFileAccess xlReadOnly
    Application.OnTime Now, Me.CodeName & ".CloseWB"
 
End Sub
 
Private Sub CloseWB()
 
    Dim oNewApp As New Application
 
    oNewApp.Workbooks.Open oWb.FullName
    oNewApp.Visible = True
    oWb.Close False
 
End Sub
 
Upvote 0
Hi Jaafar

This is the bees knees! I especially didn't know that you could change the current file access to read-only and then open it up in read mode. So after a bit of testing I'm quite satisfied that this works well on my project.
Thanks for pitching in :beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Always open xlsx files in the already-open instance of Excel
Replies
2
Views
501
nevyoung
N
S
  • Solved
Excel Macro to Print a Document In Word Works - But Hangs When Word is Already Open
Replies
6
Views
430
NdNoviceHlp
NdNoviceHlp
D
  • Solved
VBA Excel OpenDialogBox then Transfer the information to cell
Replies
5
Views
547
Daniza Morla
D
A
  • Question Question
Looking for help in Excel Macros scripts
Replies
0
Views
117
Adnan199
A
M
  • Question Question
Excel Time Keeping "Application"
Replies
0
Views
573
Mrock1
M

Forum statistics

Threads
1,225,208
Messages
6,183,593
Members
453,173
Latest member
Ali4772

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top