[ontheDB]

I've searched and been unable to find a solution to this:

is there a way to use relative links in an Excel cell ?

For example, if the cell currently refers to:

='\\Z:\directory\[workbook.xlsx]worksheet'!$F$3

is there a way to use:

='\[Workbook.xlsx]worksheet'!$F$3

(assuming the all workbooks are in the same directory)


thanks!

[savbill]

There is not a way that I know of unless you use VB to control the links. If the linked files have moved since the links were created you'll get an alert with an option to Edit Links. You can check the links and edit them by selecting "Edit->Links..." this opens a window where you can change the location of the external links contained in the workbook.

I try not to use external links for the reason it creates more overhead to maintain them and makes the "update links" warning display when the workbook is opened.

[ontheDB]

Thanks for the reply. Guess it'll explain why I couldn't find a solution here or on the internet.

So would the VBA psuedo-code would go something like:

Start
Use some function to get current directory.
Manually update each cell with the new current working directory.
End.


Thanks,

[savbill]

There's a few different ways of approching this using VB depending on what you want to do. I like to keep the code seperate from the data so write to minipulate data on the ActiveWorkbook or have the code open and act on a defined workbook. So you can refer to an absolute defined path or use reserved statements for example: ActiveWorkbook.Path, ThisWorkbook.Path ThisWorkbook would give you the path from the file the script is running in. Active Workbook whatever path the active workbook exists in. I wouldn't define outside links using VB as that just replicates the problem your having in the first place. You could have VB check a file in a specifide location and pull data into your workbook from that file. An update query or process. And you can use a Function to derive a path. I often use a function to obtain the path N folders up from the working file directory. This way I can develop on say a local directory with the same folder structure as the Server location. Then once I deploy the program it is not necessary to set or change any paths in the code.

Originally Posted by ontheDB

Thanks for the reply. Guess it'll explain why I couldn't find a solution here or on the internet.

So would the VBA psuedo-code would go something like:

Start
Use some function to get current directory.
Manually update each cell with the new current working directory.
End.


Thanks,