Why on earth can't Excel handle 2 files with the same name? [closed]

Question

This bothers me for my whole IT life - I worked with 7 different versions of Excel over 20 years now, with big changes in each version, forcing me to search where the old features are hidden in the new version - but one single thing stays solid as a rock: the disability to open two files with the same name.

Sorry, Excel can't open two workbooks with the same name at the same time.

So I'm really longing for an insight here, why this is still the case in Excel 2013, which was not even necessary to implement in Excel 95? Is there any technical or design reason within the Excel data structures or internal processings that it can't handle two File objects with diffenrent paths but the same file name? I don't want no Microsoft bashing here, I just want to understand the reason behind it.

Answer 1

Microsoft say here it's due to calculation ambiguity with linked cells.

If you had a cell ='[Book1.xlsx]Sheet1'!$G$33 and you had two books named 'Book1' open, there's no way to tell which one you mean.

This way of referring to linked workbooks by name in cells persists through all versions, and I doubt very much it will change.

Answer 2

---

YES YOU CAN!!! (But I think this is an Excel bug)

---

Try this:

1. On your Desktop right click and choose "New" => "Microsoft Excel worksheet".
2. Rename the file to "Test[1].xlsx" (the name is important!)
3. Now create a now folder on the desktop and paste a copy of the file "Test[1].xlsx" into it
4. Open both "Test[1].xlsx" via double click: Et voilà!

Now the two (same named) workbooks are open in Excel. But if you look into there "Workbook.Name"-Properties, it gets even more strange, because internally they are both renamed to "Test(1).xlsx".

That's because Excel does need the special characters "[]" internally for its formulas.

So they are (normally) not allowed for a workbook name, but a workbook which is named "Test[1].xlsx" externally can be opened anyway, what is a bug for me!

Why? Because you really get into trouble as a programmer if you want to address both of this workbooks by using "Application.Workbooks[name]", which does not fail, but delivers always the first one found by this name!

Jörg

Answer 3

For all the people who end up here, because they would like to open two Excel files with the same name at the same time:

Even though Excel itself does not permit to do so due to (certainly questionable) circumstances stated by Baldrick in his answer, there at least exist workarounds which allow to open multiple xls/xlsx files with the same name at the same time in separate Excel instances/processes.

The workarounds are explained in this thread on the How-To Geek forums.

There is even a sort of "built-in" fix with help of the setting Ignore other applications that use Dynamic Data Exchange (DDE), which works for me, but leads to errors when closing Excel and then trying to open a file again by double-clicking on it.

I had to to go for the registry fix, which works fine. NOTE THOUGH that this workaround, once applied, will prevent cross-referencing cells between ALL opened Excel tables (also those with different names), since the separate Excel instances are not aware of each other (at least according to tests I just made).

You may instead want to choose the fix which adds a new context menu item Open Separate to the Explorer and only use it if you in fact want to open two files with the same name at the same time.