AboutGyula Gulyas Expertise I can answer most questions related to macros, worksheet functions, VBA, Office automation (calling/using other MS Office programs) and geocoding using Google Maps. I have 10 years experience using and programming in MS Excel and other office applications. I have extensive experience linking and using DLLs in Excel.
Experience Macros, worksheet functions, VBA, linking of DLLs and Office automation, geocoding using Google Maps.
Question Whenever I use another worksheet in a formula in excel it always uses the complete path to that worksheet. My question is can you use a relative path to reference worksheets in excel? For example, ./worksheet.xls instead of c:/worksheet.xls. When I've tried this it hasn't worked. Thanks.
Answer As far as I can tell this cannot be done. (well, at least easily).
as per Harlan Grove:
Along these same lines, is there any way to do a "relative" link or reference, similar to what you would do with a web page, whereby, you would basically tell Excel to "look for" the file to link to in either the same directory that the current (linked) file is in, or one or two levels up?
Does this make sense? So that, using the same file names from below, you could move Y.xls and B.xls to the same directory, setup the links, then
move both of the files to D:\Z\ directory and when you open B.xls, still have it refer to Y.xls, because it is in the same directory still?
>Does this make sense?
...
Makes sense, is perfectly reasonable, and it just can't be done in Excel. A *LONG* time ago Microsoft wrote the original Excel version 1 for 512K Macs. Those beasts had a nonhierarchical file system and mostly only one floppy
drive and no harddrive, so there could only be one file open with a given base filename. And Microsoft decreed this was Good & Sufficient, and it hasn't changed at all since. Yes, all OS's under which the most recent 3 or 4 versions of Excel runs now have hierarchical file systems spanning
multiple local and networked drives, but Microsoft seems not to care to expend the resources to bring this bit of Excel functionality out of the mid
1980's.
Excel's external reference syntax/semantics preclude you from having more than one file open at a time with the same base filename. When the file is open, *only* the base filename in brackets appears in the external reference. When the file is closed, Excel prepends the drive and full
directory path to the bracketted base filename, where the drive and full directory path are where that file was more recently saved or accessed, whichever is more recent. It's that rather braindead semantics that makes
Excel so much more difficult to work with than 123 or Quattro Pro or StarOffice Calc or . . . These other spreadsheets let you enter relative paths like
+<<..\foo\bar.123>>A:A1..A:A1 (123)
and they will leave the path-and-filename as-is. Excel simply hasn't evolved from its original implementation in this respect. It's nice to imagine Microsoft fixing this, but it probably won't happen until well after they
provide built-in support for flashing text. Microsoft seems to prefer adding eyewash to adding serious features that have been in competing spreadsheets for over a decade.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
