If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above.

Open a new instance of Excel

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:

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.

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:

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.

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.

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).