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.

shajueasow · Apr 21, 2006

Welcome to the board.
Try putting your code for disabling those toolbars,menus, etc in the following event.

Private Sub Workbook_Activate()

End Sub

dewfall · Apr 21, 2006

Thanks for the response.

I already had the Workbook_Activate event working like you suggest. What happens though, is when my special macro workbook is already open and another workbook is opened, it opens under the same application object and is subject to the toolbars and menus being disabled.

If I could somehow lock up the application my coded workbook is in so no other workbook could open in it, but instead must open in a new application object, that would solve the problem. I notice when an existing workbook is open and I open a brand new blank Excel file it does this, so there must be a way. Thanks for your suggestions.

shajueasow · Apr 21, 2006

Then try the property of:
"ThisWorkbook"

Tom Urtis · Apr 22, 2006

I'd like to find a way to force them to open in another separate instance of the Excel application object.

Dim myXL As Object
Set myXL = New Excel.Application
myXL.Visible = True
myXL.Workbooks.Open Filename:="C:\Your\File\Path\YourFileName.xls"

Legacy 98055 · Apr 22, 2006

Tom's method would work but you will not have control over how many instances are opened...

So... This is the required behavior?

1. If OrpanWorkbook.xls is opened alone in it's own instance...
a. do not allow any other workbook to be opened in this instance?
b. force any other workbooks that are created or opened to be opened in a separate instance?

2. If OrpanWorkbook.xls is opened in an instance that already has workbooks opened then close OrpanWorkbook.xls and open it in it's own instance?

This is doable but it might be a bit more complicated than you think. The workbook will need the ability to re-open it'self I suppose. I have some code on this board somewhere that does this. Search for username "Right_Click" or "tstom".

Legacy 98055 · Apr 22, 2006

This will do the above in my previous post. The only limitation I placed on it was the inability to open the same workbook in another instance (as ReadOnly). If you attempt to open the workbook in another instance at the same time, the read-only version will close it'self without prompt. The code will not allow other workbooks to be opened in "this" instance but will force them to open them in their own instance. Once a second instance has been created, it will be used for all other workbooks unless you purposely open your own instance. This code in any workbook should effectively "orphan" the workbook. If you try to open this workbook in an instance that already contains one or more workbooks, this workbook will close and reopen it'self in a new instance. See the download for a better understanding.

MyLonelyWorkbook.zip

<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 pIsLoaded As Boolean

 Private Sub Workbook_Open()
 SetUpOrpan
 End Sub

 Private Sub SetUpOrpan()
 Dim Wb As Workbook
 'get an instance to "PERSONAL.XLS" if it is loaded
 On Error Resume Next
 If ThisWorkbook.ReadOnly Then
 ThisWorkbook.Close False
 End If
 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

 Public Property Get IsLoaded() As Boolean
 IsLoaded = pIsLoaded
 End Property

 '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

 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
 If Not pIsLoaded Then Exit Sub
 MsgBox "Only one instance of this workbook may be opened..."
 Wb.Close False
 End If
 WorkbookFullName = Wb.FullName
 Wb.Close False
 CreateNewOrGetExistingInstance
 pAuxiliaryInstance.Workbooks.Open WorkbookFullName
 SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
 pIsLoaded = True
 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()
 'create a VBScript file to reopen this workbook in a new instance
 'get a reference to the correct, existing instance using an
 'arbitrary workbook's fullname
 Dim WshShell As Object, fso As FileSystemObject
 Dim TempScriptFilename As String, FhWnd As Integer

 Set fso = CreateObject("Scripting.FileSystemObject")

 'build temp vbscript filename using temp folder and temp filename
 TempScriptFilename = fso.GetSpecialFolder(2) & "\" & fso.GetTempName & ".vbs"

 'temp vbscript file
 FhWnd = FreeFile
 Open TempScriptFilename For Output As #FhWnd
 Print #FhWnd, "dim wb, auxiliaryinstance, fso, objxl, starttime"
 Print #FhWnd, "set wb = wscript.getobject(" & Chr(34) & ThisWorkbook.FullName & Chr(34) & ")"
 Print #FhWnd, "set auxiliaryinstance = wb.parent"
 Print #FhWnd, "wb.close false"
 Print #FhWnd, "set objxl = wscript.createobject(" & Chr(34) & "excel.application" & Chr(34) & ")"
 Print #FhWnd, "objxl.visible = true"
 Print #FhWnd, "objxl.workbooks.open " & Chr(34) & ThisWorkbook.FullName & Chr(34)
 Print #FhWnd, "on error resume next"
 Print #FhWnd, "set wb = objxl.workbooks(" & Chr(34) & ThisWorkbook.Name & Chr(34) & ")"
 Print #FhWnd, "starttime = now"
 Print #FhWnd, "do until wb.isloaded"
 Print #FhWnd, " set wb = objxl.workbooks(" & Chr(34) & ThisWorkbook.Name & Chr(34) & ")"
 Print #FhWnd, " if datediff(" & Chr(34) & "s" & Chr(34) & ",starttime,now) > 5 then"
 Print #FhWnd, " msgbox " & Chr(34) & "operation timed out or other error..." & Chr(34)
 Print #FhWnd, " wscript.quit"
 Print #FhWnd, " end if"
 Print #FhWnd, "loop"
 Print #FhWnd, "on error goto 0"
 Print #FhWnd, "set wb.auxiliaryinstance = auxiliaryinstance"
 Print #FhWnd, "set fso = createobject(" & Chr(34) & "scripting.filesystemobject" & Chr(34) & ")"
 Print #FhWnd, "fso.deletefile wscript.scriptfullname"
 Close #FhWnd

 Set WshShell = CreateObject("WScript.Shell")
 'run the script to close and reopen this in another instance
 'the script will delete it'self
 WshShell.Run TempScriptFilename
 End Sub

</td></tr></table>

Jaafar Tribak · Apr 24, 2006

hey Tom. This is a very creative piece of work and I find it quite educational as well. Thank you for that.

However, I am finding it a bit difficult to follow the progression of your code . What's the reason behind running a code from a vbs file for example?

Do you mind summarizing the logic of the code ?... in human language please

Regards.

dewfall · Apr 24, 2006

Tom and Right_Click,

Thanks for the suggestions.

Tom, I've gotten a variation of your code to work (thanks). I think Right_Click makes a valid point about reopening the same workbook in another application object, and that could definitely be a hazard that my end users would pose.

Right_Click, you nailed my issue with your post on 4/22 at 8:40. It's going to take me awhile to figure out the code you've provided, but it looks very promising. Thanks!

Legacy 98055 · Apr 24, 2006

Hi dewfall. It's copy and paste code. You should not need to edit it but it should work in any workbook. Admittedly, this is not production code and was just a "I think I can, I think I can" curiosity to see if I could. Tusharm might throw a conniption and Nate might post a lecture.

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?

Open in a new Excel application

dewfall

New Member

Excel Facts

shajueasow

Well-known Member

dewfall

New Member

shajueasow

Well-known Member

Tom Urtis

MrExcel MVP

Legacy 98055

Guest

Legacy 98055

Guest

Jaafar Tribak

Well-known Member

dewfall

New Member

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

Open in a new Excel application

dewfall

New Member

Excel Facts

shajueasow

Well-known Member

dewfall

New Member

shajueasow

Well-known Member

Tom Urtis

MrExcel MVP

Legacy 98055

Guest

Legacy 98055

Guest

Jaafar Tribak

Well-known Member

dewfall

New Member

Legacy 98055

Guest

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