究竟为什么 Excel 不能处理同名的两个文件？

这困扰了我的整个 IT 生活——20多年来，我使用了7个不同版本的 Excel，每个版本都有很大的变化，迫使我搜索新版本中隐藏的旧功能——但有一件事一直坚如磐石: 无法打开同名的两个文件。

对不起，Excel 无法同时打开两个同名工作簿。

所以我真的很想知道为什么在 Excel2013中仍然是这种情况，在 Excel95中甚至不需要实现？在 Excel 数据结构或内部处理中，是否有任何技术或设计原因导致它无法处理具有不同路径但文件名相同的两个 File 对象？我不想在这里抨击微软，我只是想了解其背后的原因。

28051 次浏览
小开
最佳答案

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.

小开

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.

小开

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


提交答案