Open in a new Excel application

dewfall · Apr 21, 2006

Hi Folks,

First time poster here. I hope I'm doing this right.

I have a situation where I'd like to open a special workbook (loaded with code for security, disabling certain toolbars and menus, and appearances) in its own Excel Application object. I don't want other workbooks, opened before or after, to be subject to those settings, so I'd like to find a way to force them to open in another separate instance of the Excel application object.

Thanks for any good suggestions.

dk · Apr 24, 2006

Jaafar. Giving a workbook the ability to close it'self and then reopen in another instance without it becoming read-only would require some kind of third party source. For example, if the code were contained in the workbook it'self, the code to reopen could not run because the workbook would be closed before running any code to reopen. So the workbook creates a vbscript file on the fly that gets a reference to the current application object(auxiliaryinstance), closes the workbook, creates a new application object, and reopens the workbook in the newly created instance. Once opened the script passes a reference to the auxiliaryinstance property of the workbook. This auxiliaryinstance is then available to open any workbook that the user attempts to open in the new orphaned workbook instance. Clear?

Hi Tom,

I'm not sure of the necessity of the VBS. How about changing the workbook's read-only property during and then opening the workbook in a new instance e.g.

Code:

Sub TransferWBToOtherInstance()
    Dim xlApp As Excel.Application, xlWb As Workbook


    'Will "transfer" this workbook to another instance of Excel

    If ThisWorkbook.ReadOnly = False Then
        ThisWorkbook.ChangeFileAccess xlReadOnly
    End If


    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Open(ThisWorkbook.FullName)
    xlApp.UserControl = True
    xlApp.Visible = True

    'Workbook is now open in another instance so can close this one.
    ThisWorkbook.Close savechanges:=False
End Sub

In my brief test this successfully opened the workbook containing the code in another instance as read/write. Not meant to be a dig at your code, just wanted to add to a potentially interesting discussion.

Cheers
Dan

Legacy 98055 · Apr 24, 2006

Thanks DK. Your code is much better that creating a VBScript file. The only problem I am having is the ability to set the AuxiliaryInstance property in the newly opened workbook. To clarify...

An instance of Excel is running with workbook A opened.

User tries to open Orphan.xls in this same instance. The code disallows this by closing orphan work and then reopening in a new instance.

However, we need to retain a reference to the application object containing workbook A. We will use this to host any workbooks opened or created in the new instance. Hence the following property set:

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> 'should only set this reference from VBScript
 Public Property Set AuxiliaryInstance(app As Excel.Application)
 If pAuxiliaryInstance Is Nothing Then
 Set pAuxiliaryInstance = app
 End If
 End Property
</td></tr></table>

I replaced my code with yours in this procedure...

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Private Sub CloseMeAndStartMeInNewInstance()

 Dim xlApp As Excel.Application, xlWb As Workbook


 'Will "transfer" this workbook to another instance of Excel
 Application.DisplayAlerts = False
 ThisWorkbook.Saved = True
 If ThisWorkbook.ReadOnly = False Then
 ThisWorkbook.ChangeFileAccess xlReadOnly
 End If
 Application.DisplayAlerts = True

 Set xlApp = New Excel.Application
 Set xlWb = xlApp.Workbooks.Open(ThisWorkbook.FullName)

 '##########################################
 'this is where I am having a problem. I cannot set the reference
 'for some reason
 'get a ref to the current instance
 Set xlWb.AuxiliaryInstance = Application
 '##########################################

 xlApp.UserControl = True
 xlApp.Visible = True
 Application.DisplayAlerts = True
 ThisWorkbook.Close savechanges:=False


 End Sub
</td></tr></table>

Any idea why this is working from VBScript and not from Excel?

Thanks!

Legacy 98055 · Apr 24, 2006

DK. I would like to get your method to work. It's much cleaner and probably less error prone. If you don't mind, please download and extract this:

DK_MyLonelyWorkbook.zip

Open Excel with at least one workbook opened. Then open "DK_MyLonelyWorkbook.xls" in the same instance. The code will stop in the relevant procedure and you can then step through to see the error. xlWb.AuxiliaryInstance . This is a valid public property in the newly opened workbook but I can't set the property for some reason...

dk · Apr 25, 2006

Hi Tom,

Had a quick look at this. I think there is some limitation in being able to access properties/methods from another workbook or instance in this case. I had a quick search on Google Groups and found a few postings by Chip Pearson but nothing concrete.

Anyway, I did manage to get to set the variable for the auxiliary application by doing the following:

1. Insert a standard module and add this function:

Code:

Public Function AuxInstance(app As Excel.Application)
Set ThisWorkbook.AuxiliaryInstance = app
End Function

2. Next, rather than attempting to set the property within the code I called the Function AuxInstance i.e.

Code:

Application.Run xlWb.Name & "!AuxInstance", ThisWorkbook.Parent

This code executes and successfully calls the function in the other instance (which you can see if you step through). I guess in theory that you should then be able to monitor application level events in both instances but I haven't tried this yet. If you can get it to work I'd be interested to see the results.

DK_MyLonelyWorkbook.zip

I'll take a look tomorrow when I'm back at work

Cheers
Dan

Jaafar Tribak · Apr 25, 2006

Code:
2. Next, rather than attempting to set the property within the code I called the Function AuxInstance i.e.

Code:

Application.Run xlWb.Name & "!AuxInstance", ThisWorkbook.Parent

Dan

Unless I am missing something here, I think the above line would only set the AuxiliaryInstance Property of the ReadOnly version Not that of the actual xlWb!

I may be wrong but I thought the Run Method coudn't acess Procedures residing in foreign applications. !

Regards.

Late Edit : Actually, I was wrong. I have just tested the code using DK's function and it does work for ensuring the specific wb is on its own.! Only problem I have noticed is that more than 1 XL instance is created for new workbooks.

I am learning a few things here

Legacy 98055 · Apr 26, 2006

DK. I came to the same conclusion.

As far as monitoring events in both applications? It's a certainty as far as I know.

Simply changing this declaration:
Private pAuxiliaryInstance As Application
To this:
Private WithEvents pAuxiliaryInstance As Application

However, there is no need for it in this workbook. You can also assign a latebound reference to an an early bound variable declared withevents. I have found this useful at times. Thanks for the tips. The usercontrol property was also a new one for me...

DK_MyLonelyWorkbook2.zip

Jaafar,

Unless I am missing something here, I think the above line would only set the AuxiliaryInstance Property of the ReadOnly version Not that of the actual xlWb!

This is what we want. A reference to the current application hosting the readonly version sent to the newly created application which will now be hosting the non readonly version. pParentApp hold a reference to the actual parent application. The non readonly version will then have a reference to the first instance to refer to in the case of a user trying to open or create any workbooks in the new instance.

Only problem I have noticed is that more than 1 XL instance is created for new workbooks.

There should only be two instances. The instance hosting orphan.xls and the instance hosting all other workbooks...

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Option Explicit

 Private Declare Function SetForegroundWindow Lib "user32" _
 (ByVal hWnd As Long) As Long

 Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
 (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

 Private WithEvents pParentApp As Application
 Private pAuxiliaryInstance As Application
 Private pPersonalXls As String

 Private Sub Workbook_Open()
 If ThisWorkbook.ReadOnly Then ThisWorkbook.Close False
 SetUpOrpan
 End Sub

 Private Sub SetUpOrpan()
 Dim wb As Workbook

 'get an instance to "PERSONAL.XLS" if it is loaded
 On Error Resume Next
 Set wb = Workbooks("PERSONAL.XLS")
 On Error GoTo 0

 If Workbooks.Count > 1 And wb Is Nothing Then
 'PERSONAL.XLS not loaded
 'other workbooks are already open in this instance
 'open ThisWorkbook in a new instance of Excel.Application
 CloseMeAndStartMeInNewInstance
 ElseIf Workbooks.Count > 2 Then
 'PERSONAL.XLS may or may not be loaded. Does not matter at this point
 'other workbooks are already open in this instance
 'open ThisWorkbook in a new instance of Excel.Application
 CloseMeAndStartMeInNewInstance
 Else
 'this instance is ok to open ThisWorkbook
 'close PERSONAL.XLS in this instance
 If Not wb Is Nothing Then
 pPersonalXls = wb.FullName
 wb.Close
 End If
 'continue opening in this instance
 'start watching the application level events to
 'respond to any attempts to create a new workbook
 'or to open an existing workbook
 Set pParentApp = Application
 End If
 End Sub

 Private Sub SetAuxiliaryInstance(App As Excel.Application)
 If pAuxiliaryInstance Is Nothing Then
 Set pAuxiliaryInstance = App
 End If
 End Sub

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
 If Not pAuxiliaryInstance Is Nothing Then
 'if the user closes the Auxiliary Instance but we still have a reference
 'to the application, we will have an orphaned proccess running invisibly
 'this will close it
 If Not pAuxiliaryInstance.Visible Then
 pAuxiliaryInstance.Quit
 Set pAuxiliaryInstance = Nothing
 End If
 End If
 End Sub

 Private Sub pParentApp_NewWorkbook(ByVal wb As Workbook)
 'close the newly created workbook and open it in the Auxiliary Instance
 wb.Close False
 CreateNewOrGetExistingInstance
 pAuxiliaryInstance.Workbooks.Add
 SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
 End Sub

 Private Sub pParentApp_WorkbookOpen(ByVal wb As Workbook)
 'close the newly opened workbook and re-open it in the Auxiliary Instance
 Dim WorkbookFullName As String
 If wb.FullName = ThisWorkbook.FullName Then Exit Sub
 WorkbookFullName = wb.FullName
 wb.Close False
 CreateNewOrGetExistingInstance
 pAuxiliaryInstance.Workbooks.Open WorkbookFullName
 SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
 End Sub

 Private Sub CreateNewOrGetExistingInstance()
 If pAuxiliaryInstance Is Nothing Then
 Set pAuxiliaryInstance = New Application
 If pAuxiliaryInstance.Workbooks.Count = 0 And pPersonalXls <> "" Then
 pAuxiliaryInstance.Workbooks.Open pPersonalXls
 End If
 End If
 'always set the visiblility to True because the user may have closed
 'the application but it is still running invisibly if we have a reference to it
 pAuxiliaryInstance.Visible = True
 End Sub

 Private Sub CloseMeAndStartMeInNewInstance()

 Dim xlApp As Excel.Application

 'Will "transfer" this workbook to another instance of Excel
 Application.DisplayAlerts = False
 ThisWorkbook.Saved = True
 If ThisWorkbook.ReadOnly = False Then
 ThisWorkbook.ChangeFileAccess xlReadOnly
 End If
 Application.DisplayAlerts = True

 Set xlApp = New Excel.Application
 xlApp.Workbooks.Open ThisWorkbook.FullName
 xlApp.UserControl = True
 xlApp.Visible = True

 xlApp.Run ThisWorkbook.Name & "!Thisworkbook.SetAuxiliaryInstance", Application

 ThisWorkbook.Close savechanges:=False

 End Sub
</td></tr></table>

dewfall · Apr 28, 2006

Hi Guys,

Just wanted to extend my sincere thanks to Right_Click, rafaaj2000, and DK. I've gotten my application to work more or less perfectly using the solution you provided.

Rob[/list][/code]

Open in a new Excel application

dewfall

New Member

dk

MrExcel MVP

Excel Facts

Legacy 98055

Guest

Legacy 98055

Guest

dk

MrExcel MVP

Jaafar Tribak

Well-known Member

Legacy 98055

Guest

dewfall

New Member

Similar threads

Forum statistics

Share this page

Open in a new Excel application

dewfall

New Member

dk

MrExcel MVP

Excel Facts

Legacy 98055

Guest

Legacy 98055

Guest

dk

MrExcel MVP

Jaafar Tribak

Well-known Member

Legacy 98055

Guest

dewfall

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock