Relative file address

[elanus]

How can I reference a file using a relative address?

If I have my spreadsheet in TopFolder on Drive C and the file that I
want to access in LowerFolder beneath, the following absolute reference
works:

=IF(FileExists("c:\TopFolder\LowerFolder\File1.xls"),"Y","N")

How can I use a relative reference of the form
"..\LowerFolder\File1.xls"?

 

[Harlan Grove]

How can I reference a file using a relative address?

In external link references you can't. Excel doesn't provide that functionality.

If I have my spreadsheet in TopFolder on Drive C and the file that I
want to access in LowerFolder beneath, the following absolute reference
works:

=IF(FileExists("c:\TopFolder\LowerFolder\File1.xls"),"Y","N")

How can I use a relative reference of the form
"..\LowerFolder\File1.xls"?

First off, ".." in pathnames means back up to the parent directory of the
working/current directory.

If you're using the FileExists udf from John Walkenbach's site, then you can use
relative pathname arguments to it, but pathnames would be relative to Excel's
working directory, which is returned by INFO("Directory"). Excel doesn't support
relative references to any other drive/directory.

 

[elanus]

Thanks Harlan. I was trying to allow the users of my spreadsheet to
place it anywhere, rather than forcing them to hve it in the same
folder structure as mine. Relative file addressing would have allowed
the lower level folders to be found without knowing the higher levels.

I managed to achieve the same result by using Cell("Filename") to
return the current spreadsheet's location, then trimming the file name
from the end to give the path. I could then add the lower folder
structure to get at the desired file.