Open a new instance of Excel

Jong · Jul 16th 2004

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:

Code

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 · Jul 16th 2004

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:

Code

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

brewguy99 · Jul 16th 2004

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.

Code

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

Display More

Hope this helps
Steve

Jong · Jul 16th 2004

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 · Jul 16th 2004

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 · Jul 16th 2004

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

Code

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

to

Code

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 · Jul 16th 2004

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 · Apr 30th 2012

Re: Open a new instance of Excel

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 · Apr 30th 2012

Re: Open a new instance of Excel

Hi Scott

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

jon_nxt · Oct 15th 2014

Re: Open a new instance of Excel

Hi,

I have a similar case (but slightly different). I did a so-called mini-tool based on excel and VBA.
And I would need that this tool to be open in a separate session (as I also use to hide the app window and leave only a userform visible).
Thing is... user can have some other workbooks open prior to opening this one, so I cannot control DDE on each computer and I cannot even use the IgnoreRemoteRequests for each workbook.
So, my idea is (not yet completed in code so I will describe it in pseudo-code)
In my tool, there will be a check once the workbook willl open (using the workbook_open event)
This will check if the file is alone in the current excel session;
If alone, that means it has been already started as separate session or it is the first file open in excel.
In this case, using Application.IgnoreRemoteRequests = True will do the trick.

If not alone in that session, then... it means it is open in an existing instance (let's call it first session).
Save itself in a temporary copy - to a pre-established naming and location, slightly different than the usual filename.
(This will allow opening the usual file in a new session)
So, next is creating a new Excel instance where to open the original file (now available as it was saved under another name temporarily).
Then, close the current workbook (not touching other workbooks in the first session).

Another tricky part is that, once started alone in a session, the script should check for the pre-established naming and location and if exists... to delete that for future use. Or, the script can just overwrite that without prompting and then we can forget about this part.
I guess if trying to delete too early (before the initial file has closed itself) it will fail. So one can set a waiting time or delete that later on, triggered by another event (eg. Quit or Close the workbook).

What do you say? It is doable?

**S O** · Oct 15th 2014

Re: Open a new instance of Excel

Hi jon_nxt,

Please do not post on an old thread that has been inactive for so long - instead, post your own thread asking your own question and if you think it would be helpful then post a link back to this thread.

Regards,

S O

Open a new instance of Excel

Participate now!

Share