Two workbooks with the same name in EXCEL, wth?

[DarthFishy]

This surely cannot be the first time that I've noticed this but Excel doesn't allow me to have to workbooks/spreadsheets with the same name, but in different folders, open at the same time?

This seems a bit backward to me.

I just tested and I can have two Word Documents open with the same name (different folders) at the same time.

Is there something peculiar about the way Excel does file management that forces it to work this way.

 

[shuttlt]

https://blogs.office.com/2009/01/08/why-cant-i-open-two-files-with-the-same-name/

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.

Doubtless when Excel has matured as a product Microsoft will fix limitations like this.

 

[shuttlt]

Incidentally, if you Google the thread title, you get the answer to the question in the first couple of hits.

 

[DarthFishy]

Thank you.

Pretty obvious now that I think about it.

I admit I was more in a ranting mode, than an answers mode, but trust people here to put the E in ISF. (We really need to update that saying!)

OK thinking about this more, I realise that this is probably a more serious problem than I considered at first. It would be trivial for Excel itself to keep track of which workbook is which (use the full path for example), but expecting the users to do that when they are coding equations is a much bigger ask.

I wonder if there is a relatively easy solution, both in terms of usability and programmability, to this?

 

[Trebuchet]

Have you tried opening files with the same name in other apps? I'm guessing that won't work either.

 

[DarthFishy]

I did actually, tested it in Word (see OP). I also do it regularly in my text editor (I tend to organise files by folder, with similar files having the same name but in different folders).

shuttlt's response is a logical reason for Excel handling files the way they do though.

 

[Fizil]

Open a separate instance of Excel. Normally files are loaded into the same instance of the program, and you can't load duplicate file names. However, if you start an entirely separate Excel process, it should be able to load it.

 

[paulhutch]

LibreOffice Calc has no problem doing this because it uses the full path for linkage. I do this all the time using folders for organization like Darth Fishy does. As an example, nearly every firmware project uses hardware timers so in the Firmware project doc sub-directory there is a spreadsheet named TimerCalcs.ods. When I'm creating a new project based on the same micro as older projects I will often have three or more copies of TimerCalcs.ods open at the same time.

Perhaps Microsoft will check out the source code for LibreOffice so they can update Excel.

 

[shuttlt]

Maybe there is a risk that if they changed this, some crappy old application might be made vulnerable to a buffer overflow attack if it was passed a fully qualified path instead of just the workbook name?

 

[Wudang]

If you want to try VBA you can do a lot of stuff like setActiveWorkbook etc. I learned more than I ever wanted to when I inherited someone elses excel application that concatenated and processed literally hundreds of other excel files on network drives. I do things like get the current folder and work from there etc.
Hard to say if it would help without knowing more.

 

[DarthFishy]

Thanks for all the advice!

This is not a major problem for me, so I can manage to get around the problem without too many hassles I was just ranting.

I have LibreOffice at home on my Ubuntu machine so I'll try it there just to check the difference.

 

[jeremyp]

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.

Actually, yes there is a way to tell which one you mean. It's clearly the one that is in the same folder. This is how every other application works and I'm amazed the Excel team hasn't thought of it yet.

 

[Klimax]

Actually, yes there is a way to tell which one you mean. It's clearly the one that is in the same folder. This is how every other application works and I'm amazed the Excel team hasn't thought of it yet.

There is no such limitation on placement of two or more workbooks in Excel. They can be on two different drives/shares. Your method is thus fully 100% wrong and invalid fix.

Next time check how wrong you are before your write wrong nonsense and make ignorant suggestions.

Some futher links:
http://blogs.msdn.com/b/acceleratin...-i-open-two-workbooks-with-the-same-name.aspx

BTW: By OO/LO allowing duplicate names they will have problems with files using cross-file references.

 

[Wudang]

Plus what about newly created workbooks that have not been persisted in a folder so just have a name?

 

[paulhutch]

BTW: By OO/LO allowing duplicate names they will have problems with files using cross-file references.

LO/OO uses fully qualified path/file names for links to data in other files so there is no problem. e.g. ='file:///C:/Users/ph/Desktop/Untitled1.ods'#Sheet1.B3 is a cell link from another spreadsheet named Untitled1.ods that is in a different folder.

 

[Maurice Ledifficile]

I'm probably missing something that's over my head, but here goes:

Why not simply rename one of the files by adding a number or a word?

Have you tried "compare"? This could presumable allow you to open both.

 

[Klimax]

LO/OO uses fully qualified path/file names for links to data in other files so there is no problem. e.g. ='file:///C:/Users/ph/Desktop/Untitled1.ods'#Sheet1.B3 is a cell link from another spreadsheet named Untitled1.ods that is in a different folder.

Will not work for OOXML and original binary workbooks. And it is not exactly transferrable between computers. Imposes different set of restrictions on user...

 

[paulhutch]

Will not work for OOXML and original binary workbooks.

That's too bad that Microsoft's XML format and older proprietary binary formats are incompatible with the most common standardized unique file specification system, the URL. The Open Document Format for Office Applications (ODF) was developed right from the start with the idea that most stuff will eventually migrate to the web where URLs are king.

And it is not exactly transferrable between computers.

As long as both PCs access the files from the same location then it is transferable. Normally my spreadsheets are stored on network accessible shares so for every PC I log on to it works fine. My quick and dirty example done on purely local drives would be a small problem when moved to different local drives. When that happens you can use search and replace to quickly fix the no longer valid URLs.

It's been almost a decade since I left MS Office, after more than a decade of use, so my memory is fuzzy. How does Excel cope with linked files being moved or deleted from their original disk drive or network path?

Imposes different set of restrictions on user...

Absolutely as does nearly every set of applications from different companies. I find the OO/LO limitations less restrictive because I find not being able to work on files with the same name but different paths simultaneously a much worse limitation than being inconvenienced if I move spreadsheets with linked cells to a different directory structure.

BTW in LO the linked data is cached locally and if you don't choose to update when you open the file you can still see the data even when the linked file is not available. e.g. you linked to a file on a web site and your internet connection is down.

 

[paulhutch]

It's been almost a decade since I left MS Office, after more than a decade of use, so my memory is fuzzy. How does Excel cope with linked files being moved or deleted from their original disk drive or network path?

Excel prompts to correct the link location.

Answering my own question since I'm in the main office today and have Excel on this PC. Although I never use it I have it installed because the corporate licensing gives us more licenses than we use.

 

[Klimax]

Will not work for OOXML and original binary workbooks.

That's too bad that Microsoft's XML format and older proprietary binary formats are incompatible with the most common standardized unique file specification system, the URL. The Open Document Format for Office Applications (ODF) was developed right from the start with the idea that most stuff will eventually migrate to the web where URLs are king.

Sorry but it ain't really useful. You should have checked that link I posted. Or you would not be posting wrong assertions and nonsense.

Sorry, but web argument is frankly nonsensical. It doesn't solve anything nad creates even more problems. (Just number of times when URL breaks because something changed...) It is solution in search of problem, which so far has not been demonstrated to work, be useful and to survive actual use.

And ODF was for a long time badly written specification with too many pieces missing. And it looks like even good ideas were terminated by idiotic decisions.

And usefulness of web-based interlinked workbooks is strongly suspect. Is there even full implementation of such idea?

And it is not exactly transferrable between computers.

As long as both PCs access the files from the same location then it is transferable. Normally my spreadsheets are stored on network accessible shares so for every PC I log on to it works fine. My quick and dirty example done on purely local drives would be a small problem when moved to different local drives. When that happens you can use search and replace to quickly fix the no longer valid URLs.

Just because you can fix your small number of trivial files where you know content and can manually identify them, doesn't mean it can scale even to small company not to speak about large enterprises. Your idea of solution is too wrong and useless. Without proper identification you cannot tell automatically apart two files and say which one is correct to relink.

It literally is unusable in 99% of cases where it is relevant. (Also some other posts on that blog, some interesting things there)

You should check that link I posted. (not exhaustive, but will give you an idea)

Imposes different set of restrictions on user...

Absolutely as does nearly every set of applications from different companies. I find the OO/LO limitations less restrictive because I find not being able to work on files with the same name but different paths simultaneously a much worse limitation than being inconvenienced if I move spreadsheets with linked cells to a different directory structure.
...

Funnily enough none of my users encountered it and I got it once