Microsoft Excel

PingBack from http://www.codedstyle.com/why-cant-i-open-two-files-with-the-same-name/

*Surely* it's possible to code excel that if there are links to another document that has the same name as one the user is opening, it can ask the user to specify which one. Then use the process id (or whatever) to distingush between the two in the code?

If there aren't links to other workbooks, then it could allow you to open them both. And if you put a link in, again it would question which one you actually meant.

I just put v1, v2, v3 or wahtever at end of file names

You could just open an additional Excel "session" and open the second file in it.

anyone here ever heard of PATH names?

why excel don't use full path???

='[D:\Documents\Book1.xlsx]Sheet1'!$G$33

now you can have open as many documents with the same name as you want

FYI - When the referred to file is closed, the formula contains the complete path.

Couldn't the same be used for open files?

Using full path names could fix this problem BUT it would break a lot of other scenarios:

- moving the files to a different location or renaming folders

- access the files over the network vs. locally, or using different but equivalent (mapped vs. non-mapped)

I would hate to see these problems--which already affect Word's field codes--spread to Excel's formulas.

Excel has never used full path names.

I think it should be up to the user to be responsible for adjusting path names when changing the file location. Technically, doesn't the full name of the file include the path?

Why would you want to have two files by the same name anyway - far too confusing when you are using the Open Recent option.

2 Billy

an example: document saved from sharepoint to may desktop. i open the document from desktop to do som modification. but when i want to open the original document from sharepoint, i can't.

I have this problem all the time, as my bank statements are all saved with the same filename (i.e. Bank.xls). It means I can't open then at the same time to compare.

I should really change my naming format, but it does seem strange that we can't open 2 files with the same name.

What if you make it so that automatic calculations are turned off for the second workbook when it's opened, and stay off until the first workbook is closed?

This is trying to make a virtue out of a very, very old and very, very short-sighted design decision.

FWIW, Lotus 1-2-3 and Quattro Pro both allow full pathnames *AND* relative pathnames in the filename portions of references into other workbooks. Relative filenames are resolved relative to the current working directory, which in those programs (and unlike Excel) ISN'T the directory most recently displayed in File-Open or File-Save dialogs. Lotus 1-2-3 also uses much less inconvenient << and >> to delimit file or pathnames in external references.

For example, if the current working directory were D:\foo\bar, consider the following 1-2-3 formulas.

+<<D:\foo\xyz\test.wk4>>A:X99

refers to cell X99 in worksheet A in the file with full pathname D:\foo\xyz\test.wk4.

+<<C:\one\two\three\test.wk4>>A:X99

refers to cell X99 in worksheet A in the file with full pathname C:\one\two\three\test.wk4.

+<<doda\test.wk4>>A:X99

refers to cell X99 in worksheet A in the file with full pathname D:\foo\bar\doda\test.wk4.

+<<test.wk4>>A:X99

refers to cell X99 in worksheet A in the file with full pathname D:\foo\bar\test.wk4.

And all of these files can be open in the same 1-2-3 instance at the same time. What a radical concept - right out of the early 1990s! Pity Excel is mired in functionality that made sense on the 512K Macs of the mid 1980s which came with only one diskette drive and a nonhierarchical file system, so there could never be multiple open files with the same base filename back in Excel version 1. No good reason to change that functionality in the last two and a half decades? Or have the bean counters decided there's insufficient ROI fixing this?

My point is that other spreadsheet programs handle multiple open files with the same base filename in a well-defined manner. Excel is unique among spreadsheet programs in this limitation. It's not a necessary limitation. As the other spreadsheet programs prove, relative and full pathnames could be used. Rather, the Excel functionality that causes full pathname references into closed files to become base filename references when those files are opened in the same Excel instance is what's to blame, and it doesn't seem much of a logical leap to believe it hasn't been fixed because it'd be a PITA to do so.

And just to round things out, Lotus 1-2-3 can resolve expressions like

@@("<<"&Path&"\"&Filename&">>"&Worksheet&":"&Range)

even when the file in question is closed. When, if ever, will Excel provide built-in functions to resolve evaluated external references?

Kudos to Harlan Grove.  Wish my employer would provide one of those capable spreadsheets instead of Excel.

I work with files created by an automated data-extract process.  We have 10,000+ folders and each contains a "foo-bar.csv" file.  I can only have one open at a time in Excel, it's just ridiculous.

How about adding an option with the default to false.  Allow multiple files of the same name.

@Chris L.,

To be fair, Excel can handle external references to multiple files with the same base filename AS LONG AS THEY'RE **A*L*L** CLOSED and as long as they're all XLS (or maybe SYLK files too).

There's an open source spreadsheet named Gnumeric that provides a command line tool named ssconvert.exe that can create XLS files from CSV files as a batch/background process. It's much faster than using VBA macros in Excel to load the CSV files then save them as XLS files.

If you had XLS files for each of the CSV files, you could generate external references into the XLS files using the following approach.

Enter workbook pathnames in A2:A21 (e.g., c:\x\y\[foo-bar.xls]foo-bar.csv).

Enter range addresses in B1:K1 (e.g., X99).

Enter the following formula in A2.

="='"&$A2&"'!"&B$1

This will produce a text string result that looks like the formula

='c:\x\y\[foo-bar.xls]foo-bar.csv'!X99

Copy A2 and paste into A2:K21.

Select A2:K21, copy, then paste-special as values.

With A2:K21 still selected, run Edit/Replace, replacing = with =, which may appear to be a do-nothing operation but effectively re-enters these cells, which converts the text strings that look like external reference formulas into external reference formulas.

You wind up with external reference formulas in A2:K21 without having to enter all of them individually.