Udostępnij za pośrednictwem

Why can't I open two workbooks with the same name??

  • Artykuł

This is a pop-up that's sparked multi-minute-long rants which always result in a profanity-laden diatribe against seemingly arbitrary rules in general and Microsoft in particular. When I joined the company, it was with a mandate to figure out why this is the case and fix it. I'm happy to report that I've determined the answer to the first part and very good reasons why neither I nor anyone else is going to do the second. 

It all has to do with naming qualification and ambiguity resolution. Let's say you have two workbooks, BookA.xlsm and BookB.xlsm. If BookA wants to reference a cell in BookA, it can simply set one cell equal to another using the familiar =[cellref] syntax as shown below:

What if you want to reference a cell in BookB? Well that's easy, just qualify it with the name of the workbook and worksheet where Excel should go to find the cell in question as shown below:

This is a great feature of Excel which has enabled people to build up tremendously complicated systems of workbooks with simple cross-references, multi-workbook calculation chains, and encapsulated functional areas. You can even follow standard software development practices like Model-View-Controller patterns by having a Model workbook encapsulating the business logic and data, a View workbook encapsulating the UI and results display, and a Controller workbook managing the interactions. As you jump into VBA programming, ties between workbooks will only get more complicated and qualification more necessary.

But wait!! What happens if there are two workbooks named BookB.xlsm open in the same Excel process? Which workbook is meant by BookB.xlsm? The answer is that you can't know. The duplicated use of the same name has made this case strictly ambiguous. 

What can be done about that? You have two options 1.) Redesign the qualification scheme such that it is completely unique, or 2.) Disallow multiple workbooks with the same name to be opened by a single Excel process. Obviously the later option was chosen by the Excel team way back in the early days of Excel.

This is why multiple workbooks with the same name cannot be opened by Excel within a single process.

If that sounds unfortunate, consider option 1. To come up with a qualification scheme that's unique, you'd need to detach the workbook name from the qualifier. Let's say you choose a GUID-based system where each GUID is associated with a particular workbook file and that you know about all the workbooks you'll need and all their GUIDs. Now you write a big complicated workbook system with tons of GUIDs all over the place. What happens if someone makes some changes to an existing workbook and saves a copy of it. Either these two copies now have different GUIDs or the same GUID, both of which would introduce problems down the line.

  • If they have different GUIDs, then the newly saved workbook cannot be used with any old code that accesses it's entries based on the GUID. This would essentially destroy the idea of backups, as you'd be changing the workbook when you saved a copy.
  • If they have the same GUID and they contain different logic or data, then how can the workbook developer choose between these two workbooks? Well, you'd need a qualification scheme... Yikes! You can see where this cycle would go.

 The Workaround

 The only reason that multiple workbooks can reference each other is that Excel supports a Multi-Document Interface (MDI) with multiple workbooks managed by a single process and UI. So the natural cure to the resulting reference ambiguity is to disallow it by opening each workbook in a separate instance of Excel. This can easily be done by opening Excel from the commandline or start menu and then opening workbooks from within that interface.

 That's all on this topic. Hopefully this at least helps you to feel informed the next time you forget and try to open a second workbook with the same name and get that lovely warning.

Comments

  • Anonymous
    July 09, 2013
    Instead of forcing users to manually open another instance of Excel (if they're even able to figure out that they need to do this) why not do it for them? Instead of that very blunt and unhelpful error popup, it could say something like ..."A workbook with this name is already open, would you like to open this file in a new window? (yes, no)"Problem solved...At the very least the error could give the user an indication as to how to get around the problem themselves. As it is, the user would assume they need to rename their document, which should not be necessary.
  • Anonymous
    July 09, 2013
    OK, I've confirmed that opening a new window doesn't help. The document will still not open. This is really not good enough.
  • Anonymous
    April 08, 2014
    This referencing to other workbooks might be a nice feature, but I think it does not warrant this stupid limitation that you can't open 2 excel sheets with the same name from different folders. And the solution seems logical to me: when you reference another workbook by name, it should always be 'folder based' so it just references to the workbook in the current folder, which can never be 'double' because of file system limitation. An additional syntax could be added to allow referencing to workbooks in other folders relative to the current forlder such as 'dataclients.xlsl' or  '....personnelsales.xlsl'.Referencing to a workbook by name and allowing this reference to be general to 'whatever excel file is open at that time with that name' is very unprofessional. These kind of 'features' make office to unreliable to be used in any real professional applications. Just make it that 1% less user friendly so you can make it 100% more professional and less confusing and arbitrary.And seriously, that error... when you first see it, do you have ANY idea how unprofessional that seems to anyone who knows anything about software? It is ridiculous, I do not know of ANY OTHER software that has this limitation. It makes Microsoft seem really stupid, and it is not worth it, no matter what feature it enables... you HAVE to get rid of it, no matter how you do it or what feature you have to change... believe me
  • Anonymous
    May 06, 2014
    This problem only appeared once I upgraded to win7!  I had no problems opening same name files before from different folders (or not) onto a new excel window, usually as a 'Read Only' version.So come on, you call this progress Mr Microsoft? I expected a lot better!