Re: Open a new instance of Excel
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
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.
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.
Video Tutorials / Excel Dashboards Reports