I’ve discovered some really bizarre behaviour in Excel 2003, in terms of how it interprets relative links used in Excel's "Save as XML Spreadsheet" format. I’m posting my findings here in case someone else might find them useful.
So say an Excel XML file is saved (for now) to F:\Glossary\2006-10-01\MainList.xls, and we need to specify a relative path to F:\Glossary\2006-10-01\media_MainList\. We open the XML file in a text editor or XML tool.
Simple URL and Unix conventions would suggest that a dot and a slash would give you the current directory. In Excel XML, a hyperlink to this address would look like this:
<Cell ss:HRef="./"><Data>[some data]</Data></Cell>
But then saving this XML, opening in Excel, and mousing over the link shows that this points at the Excel document itself. Instead, a simple dot (.) gives us the current directory. But using dot-slash-dirname (./media_MainList) does indeed give us a relative path to the media_MainList folder.
Note: It doesn't seem to make any difference whether we use the forward or backward slash, both \ and / produce the same results.
Now things get really weird. As soon as we add the ending slash, giving us ./media_MainList/, we don't get the expected relative path to the dirname, not even if we add anything after that final slash. Say we’re trying to link to ./media_MainList/Test.bmp. We expect this:
file:///F:\Glossary\2006-10-01\media_MainList\Test.bmp
Instead we get this:
file:///F:\Glossary\2006-10-01\media_MainList\media_MainList\Test.bmp
This makes it impossible to use a relative path to link to anything in that subfolder.
I struggled with this for hours today, googled till my brain hurt, and experimented with all kinds of silliness, like ./media_MainList/../Test.bmp and such. (Incidentally, and mysteriously, ./media_MainList/../ gives file:///F:\.) What finally worked was this:
./media_MainList//..//Test.bmp
Maybe this is described somewhere in the documentation, but I sure couldn’t find it. I hope this comes in handy for someone else.
Cheers,
Eiríkr