Open a new instance of Excel

[Jong]

Can anyone explain to me how to open a new instance of excel using VBA code?

What I am trying to achieve is, IF a user tries to open a new workbook in the existing instance of Excel, the application will open a new instance of Excel, and open the workbook at that location. Up to now, I have the following:

Public WithEvents App As Application 
 
Private Sub App_NewWorkbook(ByVal Wb As Workbook) 
     'Code should go here
End Sub 

The above code is the event handler for when a new workbook is opened. I need to somehow stop the workbook from opening in my current workbook, and open it in a new instance of excel instead.

Any suggestions will help.
Thanks,

[Jong]

Okay, I've managed to create a new instance of Excel, and to create a workbook in that instance. I still haven't figured out how to load my custom workbook in that new instance of excel.

I have the pathname to the workbook.

Any help will be appreciated.


Here is my code:

Dim objExcel As Excel.Application 
Set objExcel = CreateObject("Excel.Application") 
 
objExcel.Workbooks.Add 
 
objExcel.Visible = True 

[brewguy99]

Jong,
I can't get the event to fire to trigger the App_NewWorkbook event handler.

What I did get to work was a bit clumsy, and would require that this code be put in the Workbook_Deactivate event handler of every wb that you want as a 'standalone' Excel instance.

Any workbook containing this code will force a new workbook to be opened in a new Excel instance. If the user tries to create a new workbook, it will be created in a new instance as well.

Private Sub Workbook_Deactivate() 
     'Create variables to contain the wb name and path
    Dim Wb As String 
    Dim wbPath As String 
     'Create variable to instantiate a new Excel instance
    Dim xl As New Excel.Application 
     
     'hide stuff from user
    Application.ScreenUpdating = False 
     
     'capture the new workbook's name & path
    Wb = ActiveWorkbook.Name 
    wbPath = Workbooks(Wb).Path 
     
     'Close the new workbook before it becomes visible
    Workbooks(Wb).Close False 
     
     'Open the workbook in the new instance of Excel and set visible
     'If the workbook doesn't exist (a new workbook) then
     'create a new one in the new Excel instance
     
    On Error Resume Next 
    xl.Workbooks.Open wbPath & "\" & Wb 
    If Err.Number > 0 Then 
        Err.Clear 
        xl.Workbooks.Add 
    End If 
    xl.Visible = True 
     
     'Discard the object variable and unhide from user
    Set xl = Nothing 
    Application.ScreenUpdating = True 
     
End Sub 

Hope this helps
Steve

[Jong]

This is a great help steve


Although, there is still a problem.
When the macros of the new workbook get activated, they are also copied into the old workbook.

Any suggestions as to how to get the macros to only open in their respective workbook?

[Jong]

There is another problem with that above code. Whenever you try to close the active workbook, its opens another copy of itself. When you try to close the copy, it does the same. It just keeps spawning copies.

[brewguy99]

What is your macro security setting? If it prompts you to enable/disable macros when opening a new workbook then try selecting disable. If it doesn't prompt you, then there isn't much you can do that I'm aware of other than to set your macro security to Medium.

To eliminate the behavior described in your last post, modify the code as follows:

change

Wb = ActiveWorkbook.Name 
wbPath = Workbooks(Wb).Path 

to

Wb = ActiveWorkbook.Name 
If Wb = ThisWorkbook.Name Then Goto SubEnd 
wbPath = Workbooks(Wb).Path 

and add a SubEnd: label just before the Set xl=Nothing statement

[Jong]

The problem for macros is related to the users who will be using my software.

Let's face it, not all users are good computer users. Now when the user wants to open a new workbook, he is going to click "File>OPEN" and then it will prompt to Enable macros, which they will probably do, and then all hell breaks loose

So, there is my problem.

I've also thought about not allowing a new workbook to open, but that would just cause total chaos.

I'm trying to come up with a solution, if anyone else has other suggestions, im open ears.

[ScottM]

Hi People,

Sorry for the almighty thread revival lol. I was trying to find a solution to the above problem and through searching I came across this very thread. Although the answers given didn't help me directly the problem was the closest to mine so I thought I would come back with the solution I have found.

I guess in a way it's a bit of a hack/cheat but most of the best solutions are.

I'll start off with the problem I had and the reason I needed to open any other files in a new instance of excel. I have a small excel/vba program that I use in work. It has a userform front end and the actual excel application remains hidden at all times (application.visible = false). If I try to open an excel file by double clicking on it, it won't load as the form is shown modally, if I were to not show it modally and I opened the file I still wouldn't be able to access it until the userform was unloaded/closed so this wasn't an ideal solution either.

This is where the searching began and I came across the above suggestions. In the end up the only thing I could get to work was by turning off DDE. The trouble is that the machine I use needs administrative access to change the default program arguments (If you search DDE in vista/windows 7 you will know what I mean). If I turned DDE off via excel itself it would do almost what I asked, with the exception of not being able to open the file (due to the open arguments being wrong, the ones that I cannot change). Through lots of messing around I found that if I disabled DDE then opened my program this would allow me to double click the file to open up a new instance of excel (it would fail to open the file), however, if I then enabled DDE then opened the said file it would open fine. The reason for this is that the program is loaded in one instance of excel with DDE turned off and the other instance of excel, and any others opened up, will have it turned on. The DDE option is only saved when excel is closed. Soooo.... the solution.

Turn DDE on via excel, close the excel program to save the setting. In the excel program you have with the userforms simply have the following code when the workbook is opened "application.IgnoreRemoteRequests = true". This will turn DDE off FOR THIS INSTANCE OF EXCEL ONLY, this is because the settings won't be saved for other instances until this instance is closed. Simply add the following code "application.IgnoreRemoteRequests = false" to any code that closes the application (ie anywhere in your program before "Application.quit" and this will reset the DDE back to it's current state so that it saves as normal.

This allows the following to happen...

Before opening anything DDE is on
Open ExcelProgram1
DDE for ExcelProgram1 is turned off, DDE remains on for any new instances of excel (as it won't save till the excel instance with changes is closed)
Double click on any file and it will open in a new instance of excel with DDE remaining on. This means that any other files opened will open in this instance, as long as the code doesn't change.
Close ExcelProgram1
Before closing DDE is turned back on before the "Application.quit" code, meaning that there are no changes to the default DDE status.

One small thing to add. When I open up my program I have the shortcut set to open a new instance of excel before opening the program. This is to make sure that my program doesn't interfere with any open instances of Excel. It would be a good idea to do the same, just for the program you need to keep open, if you want to use the above method.

Hope someone can find some use of this even if it did take almost 8 years lol.

Scott

[Dave Hawley]

Hi Scott

Very thoughtful of you, thanks....Oh and welcome to ozgrid