Restore MDI file handling (open all files in one window); Kill SDI (each spreadsheet opens in a seperate window)
In the good versions of Excel (prior to 2007) one could open multiple workbooks in the same window. Now, one has multiple instances of Excel (and every other Office app) whenever one opens more than one document / spreadsheet. Quite stupid.
(2016-04-27 Dan [MS] - updated title to fix MDI/SDI usage - they were previously being used "backwards")
LilBro
shared this idea
Thanks for all of the votes – the team has definitely taken notice of the activity around this issue. We moved to SDI as a result of customer requests, but it looks like we’ve got work to do to really nail the use cases people care about. From a read over the comments, I see a number of cases that we will want to investigate further as we think through MDI vs SDI. We’ll get someone from the team to take a deeper look, and we may reach out to some of you for more clarification as we go. Thanks again for all the voting and passion here!
John, Excel
494 comments
-
Jimmy
commented
For me changing this setting via regedit solved the issue:
- Go to HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options
- Set DisableMergeInstance to ‘0’For some reason it was set at '1'. I never set it like that.
-
Ken
commented
Make SDI/MDI an Excel option, this way the user can decide which approach they want Excel to use.
-
corey
commented
The one really big thing for me that sucks about MDI is that the full file path for a given workbook is not considered the "file name". Sometimes I need to work with a local copy I maintain of a shared document. With MDI, opening both my version and the common version at once in the same Excel instance will of course fail on the second one opened - "can only have one document named X open" or whatever the exact error is. So SDI lets me have both open, and I do want them separate, in two different windows entirely (both docs pull data from a database; I usually want to refresh the data in one and see the diffs between the two).
At least in Excel 2010, I can right-click on my Excel shortcut in the task bar (win7, win10) and open a new instance of Excel to open up the other version of the workbook in, thus working around MDI. That gives me enough flexibility. Haven't upgraded (home or work) to Excel 2016, so...
Sorry to rain a bit on the "MDI vs SDI" parade.
Now, being able to pick two docs in two different instances to sync the scrolling on...that might be cool/useful in some cases, when it just seems easier to visually compare rather than write a bunch of vlookup formulae between the two that I'd have to remove later.
-
Mehran
commented
David,
I totally understand. The offer is open to anyone that needs it. I know I was so angry when the changed happened just overnight and I had to rush to find a solution.Good luck.
-
David Portwood commented
Mehran - That's way over my head, Mehran. It may be a fine workaround for the Windows elite, but that's not me. Also, my db apps are not for personal use, but for end users who may not have any computer skills at all, other than basic point-and-click.
-
Mehran
commented
David,
I simply wrote my own GetWorkbook. It uses Windows API (FindWindowExa) to find all Instanes of Excel and go through each instance searching for the filename I wish to access, if found then I connect to it and return a Workbook Object as well as a value to say if the file was already open or if it had to open it (if not found, then I use the workbook.open to open the file in the same instance). I use the flag to know if my routine should close it once it has finished with it or just to close the Workbook object.I then went through my code searching for Workbooks.Open and replaced them with a slight change to myGetworkbook, and search for Workbook.close and replaced with an additional test for the flag.
-
Mehran - I have looked at a dozen homegrown workarounds and they all either don't work quite well enough or they cause other problems. Can you give a brief description of your workaround? Just the "trick". Is this the one where you make the application invisible? By the way, I run my apps from a controlling userform (often with child userforms).
-
Mehran
commented
David Portwood - I had the same problem where a master macro workbook controls multiple Excel data workbooks for analysis and to produce complex reports (never mix data and Code in the same workbook - unreliable). I have figured a way around the issue using VBA code. I would be happy to share a sample code with everyone, email me da3ecd42@opayq.com.
Microsoft making the change and rolling it out without advance testing and offering workaround for the issues raised by this group is nothing short of irresponsible and their ignoring this advance user community explains why people have such low regard about Microsoft products.
-
Greg, For me it's all about being able to use Excel as platform for database applications. Under SDI this is no longer possible because a very simple functionality has been lost. Used to be, running code from macro workbook A I could freeze the screen, open workbook B containing raw data, pull the data from B and/or process it in some way, then close workbook B and unfreeze the screen. Note that the end user will not have seen workbook B at all. In this way I can even virtually chain workbooks B1, B2, B3, ..., Bn and thereby handle a logical table containing millions of rows. In fact, there's no theoretical upper limit on the number of rows. Not bad, right? What I can no longer do under SDI is freeze the screen. As a consequence, when any workbook B is opened focus necessarily jumps to workbook B. True I can programmatically force the focus back to workbook A but there's an unacceptably ugly "flicker" during this switch. Can you believe losing the very important capability described above, for no benefit that I can see? I am astonished at Microsoft! There's stupid and there's Microsoft stupid and for my money, there's no stupid like Microsoft stupid!
-
Kary Williams
commented
Greg,
I agree that you should probably read a majority of the comments as they detail some of the problems that SDI has caused. For example, in the 2010 Exel if you have two spreadsheets open, and want to compare a total quickly, you could simply highlight the cells you wish a total on and at the bottom of the sheet you would see the total and the average displayed. Then on another instance of Excel you could open a different sheet, highlight a different set of cells, and see the Total and Average displayed at the bottom. Then you could compare the total to the total on the first spreadsheet.In 2016 Excel, when you click on the second spreadsheet the total on the first spreadsheet disappears. Therefore the only way you can compare the totals is to write down the first total and then compare it to the total on the second spreadsheet.
This is just one of the challenges that 2016 Excel aka Office 365 has caused in my work. There are many, many more.
Another, in the 2010 Excel if I have 6 spreadsheets open in one instance of Excel, and a different 6 in a second Instance of Excel, I can quickly minimize just the first instance and have instant access to the second instance (ie 6 spreadsheets tiled properly).
In 2016 Excel I have to minimize each window separately. So what took one mouseclick in 2010 Excel, now takes 6 mouseclicks in 2016. And to switch back to the first 6 now takes 12 mouseclicks, 6 to minimize the second 6, and 6 more to bring up the first 6.
So let's compare:
To switch between 12 spreadsheets on the various versions.We are starting with all 12 open. We are going to minimize 6 so we can work with the other 6 Then minimize the second 6 and bring up the first 6 again.
2010 Excel
1 Click to minimize the first 6
2nd 6 are now open
1 click to minimize the 2nd 6
1 click to bring up the 1st 6 againTotal mouse clicks = 3
2016 Excel
6 clicks to minimize the first 6
2nd 6 are now open
6 clicks to minimize the 2nd 6
6 clicks to bring up the 1st 6 againTotal mouse clicks = 18
So now it only takes 18 mouse clicks to do what I used to be able to do in 3.
Not to mention that I have to figure out how to tile 12 spreadsheets instead of 6
As you can see, it is not a simple matter of learning a new way to do things. It is a huge waste of time. It cut my productivity by at lease 25%. Fortunately, my IT manager was able to get me set up with Office 2010 again.
I hope this helps you see just a few of the challenges with the SDI debacle.
-
Mimi
commented
it's NOT a matter of learning something new. In the past, I have looked forward to Excel updates since it meant improvements that made my job easier. This issue is just one of the many horrible things that don't work in Excel 2016. Autosave is also big problem with multiple workbook linked models. In my case, Excel gobbled up more and more private memory and froze 10+ times a day. I lost so much work! IMO MS shoved this edition out before it was ready and are still fixing serious bugs in it today, 5 years later. if 2019 isn't any better, I will be switching to something else when my 2010 support runs out. there ARE other spreadsheet products out there.
-
db22
commented
Greg Chamblin, you really should read more of the comments. It is not a matter of not wanting to use something new. The biggest problem imo is all the screen real estate used up by having a separate tool bar for every single document opened. For those of us who have to compare multiple documents at a time, this makes Excel very difficult to use. Our screens are filled up with toolbars instead of the numbers we need to look at.
I love new things, but I had to go back to Excel 2010 from Excel 2013 because I literally couldn't do my job. I was recently forced by a corporate IT upgrade to go to 2016. My supervisor has now purchased everyone in our department huge monitors to compensate for the loss of screen space. SDI still a PITA.
With MDI, it was always possible to open a new instance of Excel for a new document if that was what was needed.
I doubt that giving people the option of MDI or SDI is impossible. I think it's just a matter of Microsoft thinking that they don't need to bother.
-
Greg Chamblin
commented
Personally, I disagree with the blanket statement to kill SDI in favor of MDI. Most people I work with now, and have worked with in the past get very frustrated with multiple workbooks in a single window. I have seen comments about using formulas that point to sheets open in other windows not working, I have to wonder why. I just built a vlookup from one workbook to another using office 2016 last week and it works perfectly. I have to wonder if there are environmental restrictions or settings in place from one's organization that affect that.
I do understand the issue with closing a linked notebook, perhaps the Excel team could simply throw up a message that says something to the effect of "The file you are attempting to close is linked to another open workbook. Are you sure you want to close this file?" It's not exactly elegant, but could that help in the interim?
When I read some of these comments they read like "Give me Windows XP and Office 2010 back because I don't want to learn anything new!" when I read about some of these issues, I would be frustrated, sure, but I would enjoy the adventure of working through it, and finding a solution. When things change and break what I had done in the past, it is a perfect opportunity for me to ask myself is/was there a better way to do what I did. Many times it results in a much better solution than whatever I had cobbled together before. When it doesn't, it would be nice to have a path at Microsoft to get deeper support. Sometimes the community is good, but things always get cluttered and aren't normally well organized.
25 pages is a lot to read through, so if there were more detailed explanations of issues that absolutely cannot be resolved, I apologize for not reading them. This is just my 2 cents.
-
I hate seeing comments like, "Please give us the option!" They probably can't give us the option. Might as well ask instead, "Please give me six extra inches!" I'm sure they would if they could, but I don't think they can. Asking for the moon along with a return to MDI doesn't help us get back to MDI.
-
Anonymous
commented
Microsoft: GIVE US THE OPTION! We are not stupid! I will gladly switch back to 2007 or (gasp) open office if you don't!
-
Kim
commented
I recently moved from Excel 2010 to 2013 and was excited that I no longer had the issue of not being able to use formulas across different workbooks unless they were opened in the same instance. I just moved to Excel 2016 and am disappointed that I'm having this problem again. Is this because it changed from SDI to MDI and back again?
-
Lewis: (I mention this response to Lewis because I think it has bearing on what could be done before under MDI but can no longer be (conveniently) done under SDI.) You should not be receiving any formatted forms, just tabular data in workbooks with first tab containing the data. First row column labels. Get these from Outlook and drop them into a dedicated folder. Write a simple Excel app that opens each wb in the folder and integrates the data into a central database. From this central database, you generate any desired reports. This is simple but very powerful code which could easily be programmed under MDI, but is at least ugly under SDI. Ugly enough that I won't do it. I hate you, Microsoft!
-
Lewis Smeby
commented
This issue affects me in the following ways.
I receive numerous forms via email for reports and have macros located in two separate Personal workbooks (each associated with different departments) which will automatically open when Excel opens. Generally, I get in upwards of 5 forms per day to process which are processed based on a scheduled task. Every time I open one of these forms via Outlook, it re-opens the personal workbooks and this is a major inconvenience to me. As all instances of Excel also open with the same color scheme, if I open 2 files without closing prior instances I can easily close the wrong personal workbook instances making some macros inaccessible. This issue has my vote for a fix!
-
Marcia Kastrup
commented
Please, again, restore MDI. Enough of wasted clicks, wasted real estate, wasted patience! Back with the higher contrast too. MS ruined Excel. Please listen to us!
-
Mimi
commented
If you have commented here and not voted, please login and vote!
If anyone has any news of this topic from elsewhere, please share.