Utilising dual monitors with Microsoft Excel 2003
I often work with dual monitors and recently needed to compare two Excel spreadsheets side by side. I found that although they showed as separate instances on the Windows Taskbar I couldn’t have them displayed on separate screens. After some investigation I found that to do what I wanted I had to force Excel to open each document as a separate instance of the application. To do this try the following:
In Explorer go to Tools > Folder Options.
Choose the File Types tab then find XLS under Extensions. Highlight it and click the Advanced button.
Select Open then the Edit button.
Now in the Application used to perform action field go to the end and add a space followed by "%1". Be sure to include the quotes. You also need to unselect Use DDE. OK all of your changes. Now when you open multiple Excel spreadsheets each will be a separate instance of the application so you can move them to separate monitors. The downside is that you’ll use more of your PC resources.
This issue has been addressed in Excel 2007 beta 2.
Technorati Tags: Excel
August 31st, 2006 at 3:37 pm
“Now in the Application used to perform action field go to the end and add a space followed by “%”. Be sure to include the quotes. ”
Error in text above?
“%” doesn’t work, but “%1% shown in screen shot above text does work.
Thanks for the tip, it is helping me a lot!
Jason
August 31st, 2006 at 4:06 pm
Thanks for spotting that! It should actually be "%1". I’ll update the post.
August 31st, 2006 at 11:36 pm
If you’re already running Excel and you hold down on the control key and then start excel again, you get another instance of excel running in it’s own window. There’s an additional trick which I go into further at How to open Excel in two windows
August 31st, 2006 at 11:41 pm
However, doing it the control key way, the copy and paste doesn’t really work between the two instances of Excel. The data being copied loses the “excelliness” as if it went through a text only transformer. Does your way behave the same?
September 8th, 2006 at 4:45 pm
Very nice tip. It’s unfortunate that M$ missed this…but I suppose as long as they understand, and it’s being addressed in the next version, all is well. =)
Thanks again!
September 20th, 2006 at 8:55 pm
OK. Implementing the workaround as Ant suggested (including the 8/31 fix Ant posted - “%1″) allows me to get two separate spreadsheets up and running-(Hooray!) BUT… with this change I cannot open “.xls” documents outside of Excel. If I try to open a .xls file from anywhere other than when I am in the application (after opening Excel directly), I get this error message, ” ‘C:\Documents.xls’ could not be found. Check the spelling…..file location is correct.” On my laptop (XP Home) if I click the “x” on the error message, I get additional error messages for each of the excel documents in the directory. Finally, after all of these error messages are “x’ed out”, the spreadsheet I wanted to open finally opens. GRRRR!!!!. I never saw this on my main PC (XP Pro) because I have too many spreadsheets saved and I would be “x’ing out” for several days. LOL
Seriously, this is making me crazy so I would like to restore the system to the defaults in the advanced edit screen BUT I can’t find the sheet of paper I wrote them down on.
So: if anyone reading this has their ORIGINAL settings intact or saved what was in the boxes before attempting this workaround, please post them here or send a screen shot to me at a57chevnut at yahoo dot com. I would be most appreciative.
Lastly, here are the settings on my screen in the “Editing action for type: Microsoft Excel…”
Action:
&Open
Application used to perform action:
“C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e %1
Use DDE
Box is checked ( I never could get this to stay unchecked on either PC)
DDE Message:
[open(”%1″)]
Application:
Excel
DDE Application Not Running
Box is Blank
Topic:
system
TIA
September 21st, 2006 at 7:24 am
Have you tried %1 in quotes? I.e. “%1″. When you make this change also uncheck Use DDE before selecting OK.
If that doesn’t help then the default settings are:
Action: &Open
Application used to perform action:
“C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e
Use DDE is ticked.
DDE MEssage: [open(”%1″)]
Application: Excel
DDE Application Not Running: blank
Topic: system
September 21st, 2006 at 11:43 pm
Hey Ant, thank you so much for posting this information. I’ve been trying to get Excel to do that for some time.
I had the same problem as Cris when trying to fiddle with the open settings. It was really stressing me out.
I ‘did’ make sure to copy all of the default values in case I messed it up….but…
A problem with setting the ‘Application used to perform action:’ back to the original is that the %1 is re-added for you!
As soon as you alter it, you seem to be stuck with the %1.
Just re-open the setting after you remove it and save to see it back again.
Quotes will therefore be required, as most people will need to open files with spaces in the path.
Microsoft made sure of that, eh?
September 27th, 2006 at 5:47 am
Hi Ant and fellow Excelnuts!
))) !!!! My thanks to Ant for posting this solution and staying with it until it worked for folks like me. THANK YOU!
Success….
I tried Ant’s suggestion to put quotes around %1 in this area:
Application used to perform action:
Now the line looks like this:
“C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e “%1″
As soon as I did that, I could finally uncheck DDE. The software wouldn’t let me do that before. As soon as I unchecked DDE this time, the other boxes below that disappeared so I clicked on “OK” all the way out and started testing to see what happened.
This all came about after I attempted to restore the settings to the default or original settings before I started messing with them. I had the same problem as Jess. If I removed the %1 from the line:
Application used to perform action:
“C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e %1
The software wouldn’t allow the deletion to stick.
So I went back to the Edit screen and added the quotes per the above, was able to uncheck DDE, and the rest is history.
Someone a lot smarter than me (Ant??!!!) will hopefully deal with and solve this problem (about not being able to get back to original settings).
In the meantime, I’m happy because I now can open as many Excel windows as I want. PLUS, no more error messages when I open Excel documents.
September 27th, 2006 at 7:30 pm
You say that this issue is resolved in Microsoft excel 2007 beta. Can you explain?
September 28th, 2006 at 6:28 am
Hi Avi, my understanding is that with Excel 2007 you can simply move your spreadsheets to separate monitors in the same way that you can currently move Word docs in Word 2003.
October 3rd, 2006 at 1:33 am
I find that I can get two instances of Excel running simply by holding down the shift key whilst clicking on the application shortcut.
October 26th, 2006 at 3:47 pm
Does anyone else have a problem with the second instance of Excel giving the error message: “PERSONAL.XLS is locked for editing”?
October 29th, 2006 at 8:57 pm
Thank goodness I can have two windows open in Excel, but turns out PowerPoint 2007 can ONLY have 1 open. No matter what I try.
November 1st, 2006 at 4:54 am
I have similar error message as Arkayngel when I already have a excel window open (without shift click) and then use shift click to open another workbook.
“Cannot open Microsoft Office Excel Add-In for editing. Please edit the source document instead.”
However if I use shift click on the application shortcut (not on the excel file itself) to open two instances of Excel, it works fine. I personally prefer this method as I don’t have to change the setting and I have both options when open the excel files for viewing on two different monitors.
Instead of modify the “Open” action, maybe we can just create a new action called “Open in new window” with the modified command line. When we right click on an excel work book we will also have both option to open it in the normal way and open in a new window.
December 18th, 2006 at 3:52 pm
Hi Ant,
Thanks for the tip! One other thing I would like to be able to do. Can you open each tab of a single workbook in its own window or on its own screen? So that if you are working with a complex, multi tabbed spreadsheet, you can enter data on one tab and see it effecting things on other tabs?
Any help is appreciated.
Thanks,
Alex
January 3rd, 2007 at 6:08 pm
I can’t get excel to accept the change of adding the unselecting the Use DDE. Each time i accept changes and close, then go back in, it is always checked. What gives?
January 5th, 2007 at 9:42 am
Yep, I get the same problem of not being able to delete the “%1″, and now I can’t open up any excel documents in our document management system, which is really quite worrying…
Does *anyone* have a fix for this?!
January 5th, 2007 at 4:40 pm
I ran excel /unregserver and excel /regserver got rid of the %1, but it still doesn’t work. It is most odd!
January 23rd, 2007 at 5:31 am
Hi guys
I’m not sure I’m in the same problem as you but I found this website while searching for solution for my Excel problem and I’m really confused after reading so many technical messages here.
The problem:
I’ve lots of excel files (contracts & agreements). Have been using them since 2005 with no problem. But just yesterday, when I double clicked the file from its folder, the error message that Excel gave me was: C:\Folder Name\File Name could not be found. Check the spelling of the file name, and verify that the file location is correct.
But the location and file name are definitely correct since I’ve been using it for years.
Any solution please? Really appreciate the help. Thank you.
January 25th, 2007 at 4:59 pm
I am having the same problems that Donna is having, I am trying to change back to defaults. Example removing the “%1″, and it lets me remove it but then I go back into the editing againg ant %1 is back in there without the quotes. I keep getting the error check the spelling. Is there a regeistry entry that this might have changed. let me know thanks
Shawn
January 26th, 2007 at 5:41 pm
Thank you SO much for this post! I work with several Excel reports on a daily basis, using dual monitors, and I have been struggling with this for months - YOU ROCK!!!!
January 29th, 2007 at 8:36 pm
w00t! This little tip saved me so much heartache… thanks!
February 1st, 2007 at 10:58 am
@Donna & SHawn:
http://support.microsoft.com/kb/207574/en-us
This one seems to be related to solution in this forum.
February 15th, 2007 at 12:55 am
Thanks
February 16th, 2007 at 1:47 pm
THANK YOU ALL!! I needed to open excel across multiple monitors. I asked this question on Yahoo! Answers and “Benjamin M” pointed me here. It works. I can’t tell you how excited I am. Thanks all.
February 23rd, 2007 at 8:27 pm
I could always open multiple instances of Excel, just by using the “file, open” command within a running instance of Excel and have them running on seperate screens. The problem is that formulas cannot be copied from one instance to the next. All data is copied as text, even if I “paste special”.
I can only successfully copy formulas from one window to another window within a single running instance of Excel. I tried ANT’s solution in hopes that it would fix the problem for multiple running instances….no change.
DDE does remain checked, no matter what I do, is that the problem?
February 27th, 2007 at 10:56 pm
I came across a good little article on multiple monitors and MS office at CineMassive Displays. checkout their “resources section”. Good stuff. Definitely worth a quick read.
March 12th, 2007 at 5:18 am
How does one apply this fix on Office 2007 when running Windows Vista? The above advice is for WindowsXP, but doesn’t seem to translate over for Vista.
April 10th, 2007 at 8:12 pm
Assuming that you have an nvidia based card - if you want to just ‘maximize’ excel across both monitors make sure to turn on nview desktop manager. Now if you r-click on an application in the taskbar (or it’s own title bar) you can ‘maximize to desktop’ or send the application instantly to any monitor you want.
I assume that other video cards have similar capabilities.
April 25th, 2007 at 6:45 am
Hi, Please I have a problem in my excel. May it is not to difficult but in fact I have no solution to it and waiting ur solutions. It is that, when I open any excel sheet it is open as blank one and I need to go to file menu and press open and go to the path of the sheet to open it so please any help to not loss this time waiting.
May 10th, 2007 at 9:51 am
Great tip. Thanks a lot - Really helpful.
Have you guys a mailing list?
May 16th, 2007 at 8:05 pm
I can’t keep the dang Use DDE box unchecked. I make the changes, uncheck the box. Click ok out of all the Folder options boxes. Then even before I open excel if I go back in the Use DDE box is checked again. Excel is lcosed during tbgis whole process. Any ideas what I am doing wrong?
Thanks,
Noel
May 23rd, 2007 at 5:31 pm
WOOT! Leave it to people other than microsoft to provide the best answers! (and not just to tell you “buy office 2007″)
Thanks much!
May 24th, 2007 at 11:50 am
Hi,
Ive come back to this page after I got a new laptop.
I now also have the same problem as above, the fix is not working because the ‘Use DDE’ Box is always checked when I return. Are their any other parameters I need to change.
Ali
May 24th, 2007 at 12:03 pm
GUys.
I resolved my earlier probem, which may also help you.
You need to select and edit ‘Open’, and not ‘New’ which is at the top. When in the ‘Edit File type Screen’
Nazim
June 6th, 2007 at 1:48 pm
Guys,
This fix is not working for me; however, I tried it on my co-workers computer and it worked.
The only difference between us is that he has Administrative privileges and I do not.
Funny thing is though that in both case the “USE DDE” box will not remain unchecked.
Wade
June 6th, 2007 at 8:38 pm
Is there any setting or VBA code that I can use in a workbook to ensure that when someone opens it, it will open in a new Excel session?
Steve
June 22nd, 2007 at 9:56 am
OK. I’ve come up with a solution for the more advanced. My issue is that i have an application written in VBA in Excel and i need two things to happen related to this topic.
The first thing is that when a user opens my application i need my application to open in a seperate session if there is already a workbook open. Otherwise when my userform displays, they will be unable to continue working on their own spreadsheet. My workaround for this is to count the open workbooks and if more than 1 (my app) re-open the app in a new session.
This code can run from the from a macro called auto_open in any module:
Public Sub Auto_Open()
If Workbooks.Count > 1 then
Dim XL as New Excel.Application
set XL = createobject(”Excel.Application”) ‘ this creates a new session
XL.workbooks.open (”C:\myApp.xls”) ‘ open my app in the new session
XL.Run “myApp.xls!Auto_Open” ‘ run my auto_open macro
set XL = nothing ‘ lose reference to new session to allow session to close
workbooks(”myApp.xls”).close False ‘Close this session’s workbook
End if
End Sub
The 2nd thing i need is that while my app is open, any other excel documents that are opened need to be thrown into a new session as well. I achieved this by creating a class module for the application object to capture another workbook opening. If you are getting lost, don’t worry! Just follow my instructions. Create a new Class Module, rename it as ‘clsApp’ and enter the following code:
Public WithEvents App as Application
Private Sub App_Workbook_Open(ByVal wb as Workbook)
If wb.name “myApp.xls” Then ‘check it’s not my app opening
Dim xl as new excel.application
end if
End Sub
June 22nd, 2007 at 10:05 am
Sorry - i submitted by accident! I’ll continue where i left off…
Public WithEvents App as Application
Private Sub App_Workbook_Open(ByVal wb as Workbook)
If wb.name “myApp.xls” Then ‘check it’s not my app opening
Dim xl as new excel.application, strName as string
strName = wb.fullname ‘ store the workbook path
set xl = createobject(”Excel.Application”) ‘create new session
xl.visible = true ‘make sure the new session is visible
wb.close ‘ close the workbook
xl.workbooks.open strName ‘ Open the workbook in the new session
end if
End Sub
Once you have the class module created, you need to add the following line to the top of the module we created previously (with auto_open):
Public myApp as NEW clsApp ‘ This creates a copy of our class app
You also need to amend the Auto_Open Procedure we wrote earlier with the Else statement shown below:
Else:
Set myApp.App = Application ‘assign current application to our class
End If
So, now you’re all confused (I apologise!!) the original module should look like this:
Public myApp as NEW clsApp ‘ This creates a copy of our class app
Public Sub Auto_Open()
If Workbooks.Count > 1 then
Dim XL as New Excel.Application
set XL = createobject(”Excel.Application”) ‘ this creates a new session
XL.workbooks.open (”C:\myApp.xls”) ‘ open my app in the new session
XL.Run “myApp.xls!Auto_Open” ‘ run my auto_open macro
set XL = nothing ‘ lose reference to new session to allow session to close
workbooks(”myApp.xls”).close False ‘Close this session’s workbook
Else:
set myApp.App = Application ‘ Assign current application to our class
End if
End Sub
So with just a module and a class module we have acheived a workbook that runs entirely in its own session at all times.
Good luck and sorry for the confusion!!
sugarflux
www.broox.co.uk
June 26th, 2007 at 3:00 pm
Hi Alex,
You asked: “Can you open each tab of a single workbook in its own window or on its own screen? So that if you are working with a complex, multi tabbed spreadsheet, you can enter data on one tab and see it effecting things on other tabs?”
This one isn’t too much hassle. Just go to Window>New window and a new window will appear of the same workbook. You can then go to a different worksheet in the second window. Then go to Window>Compare side by side with… (or just position the windows manually) to see two worksheets at the same time. Changes made in one are updated instantly in the other.
Hope this helps, Mat
June 26th, 2007 at 9:58 pm
It makes sense, but it looks like using this fix/hack/whatever does not allow a second instance of “personal.xls” for macros.
I’m not well versed with excel, but I don’t think this will pose an issue for using already created macros document-to-document. but it may cause issues when you are trying to create a new macro.
Solution would probably be to close all instances of excel and just open the one you are using to create the macro.
July 3rd, 2007 at 10:32 pm
Do you know of a way to do the %1 thing in Vista? The file extensions mapping in Vista works differently - I can’t modify (or seem to fin the place to modify) the command-line parameters to add the %1. Also, I am using Office 2007 with Vista. Thanks for the great tip.
July 4th, 2007 at 7:08 pm
Everytime I open an Excel file now, I get the message that the Personal.xls file is locked for editing by “my user name”… I know Personal.xls is for storing the macros, so how do I get rid of this message and does this mean I can’t create any more macros?
July 18th, 2007 at 8:51 pm
Has anyone solved the Vista problem? I have XP and am VERY thankful for your tip, but my boss is using Vista and I would love it if he would stop whining about not being able to use the 2 monitors
July 27th, 2007 at 5:35 pm
Ant - thanks. Since my “Use DDE” box wouldn’t remain unchecked, the comment above you left on “September 21st, 2006 at 7:24 am” solved things for folks I support with one change… where you wrote “DDE Message: [open(”%1″)]”, I had to change that to {open(”%1″)} as the brackets were opening my debugger. So, the settings that worked for me were:
Action: &Open
Application used to perform action:
“C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e
Use DDE is ticked.
DDE MEssage: {open(”%1″)}
Application: Excel
DDE Application Not Running: blank
Topic: system
Thanks again for your help.
GeneH @ http://genehuller.com
July 31st, 2007 at 5:41 pm
Wow! This really works! Easy to modify, difficult to find to this info!
Too bad this isn’t by default but I sincerely appreciate the help on this. Now I longer have open a blank worksheet and then open the file.
If I have one Excel window open, there is at least another one or more open. With dual or single monitors, this is a perfect solution.
I had to resort to using Open Office’s Spreadsheet. While I am a fan of opensource programs, this OO2.2 spreadsheet resulted in way too many errors, particlularly entering a cell’s data (linking) from another open SS. This caused a lot time and grief! I use Excel for tax prep and can’t afford erros!
This little tidbit of info saved me $200 from buying Excel2007, when Excell97 and 2002 work just fine for me.
While this is a minor inconvenience, before knowing this, I must say kudos to MS for a great, flawless (in my opinion and use) product with Excel.
If you read this and do use OO2.2’s SS, be prepared for trouble if converting from Excel to OO SS.
tt
August 7th, 2007 at 7:08 pm
Ant,
A user (Excel 2003 - XP) told me he tried your solution but says that every time he shuts down his computer, he has to make your suggested change.
What is he doing wrong? Unfortunately, since I do not have two monitors, I cannot test it myself.
Thanks!
August 27th, 2007 at 3:51 pm
This hack brings me much joy. I have to wonder why Microsoft doesn’t have problems with Word out of the box but Excel seems to think that multi-monitor might as well not exist.
PS This works fine with Excel 2007 as well.
September 5th, 2007 at 9:22 pm
Has anyone solved this Personal.xls problem? I love using multiple instances with multiple monitors. But that personal.xls error is for the birds. Has anyone figured out a way to fix this? I use macros across multiple excel workbooks so deleting the personal.xls is not an option.
September 7th, 2007 at 7:39 pm
In order to open a second copy of Excel 2007 on a computer running Vista, press and hold the shift key when clicking on the Excel icon. This will open a second instance of Excel 2007. This is very helpful when your second screen isn’t the same size as the primary screen (such as a laptop) and isn’t next to the primary monitor).
September 7th, 2007 at 7:43 pm
In order to open a second copy of Excel 2007 on a computer running Vista, press and hold the shift key when clicking on the Excel icon. This will open a second instance of Excel 2007. This is very helpful when your second screen isn’t the same size as the primary screen (such as a laptop) and isn’t next to the primary monitor).
To open the spreadsheet in this new instance, open Windows Explorer, find the excel document and then drag and drop it onto the blank Excel application.
Hope this helps as it has worked for me.
September 10th, 2007 at 6:28 pm
for those having trouble with the personal.xls file generating an error when you open two instances: try setting the personal.xls to read only.
I no longer get an error.
September 29th, 2007 at 6:16 pm
Thanks for all the help… Why does MS change things like this?
September 30th, 2007 at 4:24 pm
“Why does MS change things like this?” - because times change, software is developed, and interfaces and options change along with it.
Otherwise, we’d all still be running Visicalc on a TRS-80…
October 11th, 2007 at 12:14 am
Thanks for your help! I’ve been looking to open excel spreadsheets in seperate instances of excell for a while. Figures it wouldn’t be as simple as an option in the menu!
October 23rd, 2007 at 2:46 pm
using the %1 with Vista and Excel 2007, this is what I did: using regedit change these keys: hkey_classes_root\excel.sheet.12\shell\open\command
edit default and add the “%1″. the next key under command is ddeexec remove [open(”%1″)] from default. you will also have to make the same changes to hkey_classes_root\excel.sheet.8… same as above. Hope this helps.
November 1st, 2007 at 9:29 pm
In 2003 sp2 in options->general, you can select “Ignore other applications”. Then if you open a worksheet from the explorer window it’ll open as a different window (as a separate process). If you open from excel itself it’s still the same window.
November 7th, 2007 at 3:02 pm
Thanks! You are my hero for the day.
November 18th, 2007 at 6:06 pm
This works for Excel 2007 in compatibility mode (essentially saving docs in the old 2003 XLS format, which I prefer to do so that everyone I work with can use the docs. rather than dealing with some proprietary bs).
December 20th, 2007 at 2:57 am
I have changed my filetype and excel over dual monitors works well. Thanks for the info. Only problem now is i can’t use Sheet Copy/Move “To Book” as no other workbook names are displayed. Can anybody assist with vba or other solution to be able to copy/move sheets across multiple excel instances.
January 9th, 2008 at 1:02 am
To answer your question, Tammyl:
Once you get the Excel files to open as separate instances (and hence on different screens) you may run into problem copying/moving worksheets between different workbooks (files) because worksheets don’t copy/move across instances.
The way to get around it is to pick an instance of Excel, use “File -> Open” or drag and drop the file icon to have multiple workbooks opened in the same Excel instance. Of course you can’t put the workbooks in the same instance on different monitors (back to the original setting). So, do it only when you want to copy/move entire worksheets between files.
January 11th, 2008 at 5:21 pm
All of my Excel work involves VBA so I created a module I insert in all of my workbooks. I invoke two subs, one in Workbook_Open and the other in Workbook_BeforeClose. One sub “isolates” Excel from opening additional workbooks (unless you explicitly use File–>Open menu). The other “exposes” it once again upon close.
This is accomplished by using VBA to set an Excel Application flag “IgnoreRemoteRequests” to True or False. This flag can also be set manually in Tools–>Options (General Tab)–>”Ignore Other Applications”
“IgnoreRemoteRequests” is an APPLICATION flag which means it alters the functionality of your copy of Excel. It is not a workbook flag and therefore is not saved with the workbook. The state of all application flags are only saved upon exit of Excel, and the last instance closed will overwrite all flag settings of the previously closed instance. The settings of any already open instances of Excel will not be “seen” by opening another instance. Also, this setting will be for your installed copy of Excel only. Sending someone one of your workbooks will not change their Excel settings (unless your workbook contains the VBA that changes the settings.) *
*Note: our workstations have local copies of Excel installed. I don’t know how application settings are saved in a shared environment.
It can get confusing. Say you have no copies of Excel open and the current state of Ignore=No. You open a workbook containing the VBA subs which set Ignore=Yes. (You can see the “Ignore” box checked in the options menu, General tab.)
You Dbl-click another workbook, one that does not have the VBA subs. It will open in a new instance of Excel because the first one open is now ignoring open requests. But, if you look in the options menu you will see the second Excel is not ignoring requests. If you Dbl-Click a third workbook it will open in the second instance of Excel, not in a new instance and not in the first one.
You decide to close both Excel instances. Whichever instance you close last will be the settings used the next time Excel opens. This is REGARDLESS of whether or not you save any workbook changes — remember, this is an application flag not a workbook flag.
If you close the first instance last the Ignore flag will still be set to True. This can be a problem. The next time you Dbl-Click an .xls file, or attempt to open an Excel mail attachment, etc. you will only see an empty blue window. Excel is ignoring the open request!
This event can be a surprise. It appears as if Excel is “broken.” Not to worry, simply use File–>Open to open your workbook, then go to the Options menu and uncheck the “Ignore Other Applications” box.
To automatically prevent this situation from happening, if you turn on the “Ignore” flag upon starting Excel it is imperative that you turn it off before exit. Here are the subs that accomplish these tasks:
‘ (put in ThisWorkbook module)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘
ExposeExcel
‘
End Sub
Private Sub Workbook_Open()
‘
IsolateExcel
‘
End Sub
‘ (put these in Module1)
Global Const Yes As Boolean = True
Global Const No As Boolean = False
Public Sub ExposeExcel()
‘
‘ accept remote requests (double-clicking a workbook file will now open in this
‘ instance of Excel instead of starting a new instance)
‘
Application.DisplayAlerts = No
Application.IgnoreRemoteRequests = No
Application.DisplayAlerts = Yes
‘
End Sub
Public Sub IsolateExcel()
‘
‘ ignore remote requests (double-clicking a workbook file will now start a new
‘ instance of Excel instead of opening in this instance)
‘
Application.DisplayAlerts = No
Application.IgnoreRemoteRequests = Yes
Application.DisplayAlerts = Yes
‘
End Sub
Note: if ever you are debugging or working in Excel and your VBA subs stop due to an error, (or you set Application.EnableEvents = No while in development phase) it is possible the ExposeExcel sub will not run on exit. You will know the next time you Dbl-Click an .xls file and Excel comes up blank! Simply do File–>Open as described above.
February 8th, 2008 at 3:16 am
thank you for this great tutorial about how to automatically open multiple instances of microsoft excel !
February 11th, 2008 at 2:44 pm
Rich,
In using your registry change, separate instances of Excel 2007 in Vista do open. However, the files themselves that I’ve opened Excel with do not actually open. Is there an extra step I’m missing? Also, it would be FANTASTIC if there was a way to give users the option of switching between having spreadsheets open in separate windows or in the same–for instance, have 2 open in one window, and another 2 in a different window.
February 14th, 2008 at 9:08 pm
Works great. Had to set this for both Excel 2003 and 2007 documents, as i open both the old and new….
Thanks.
February 29th, 2008 at 9:42 am
Great help! This Excel problem has been one of my major frustration, now you have helped me solved it.
More power to you!
March 14th, 2008 at 2:45 pm
Now is there any way to do this in powerpoint as well?