Get relative Path: advanced workbook linking.

[dakke]

This might be a little off topic, but I'll ask anyway.

I work on multiple workbooks. They all calculate a lot of functions and I am
forced to create multiple workbooks to do these calculations. Linking to
other workbooks basically displays the results of workbook1 and works from
there.

But...

I need to keep an overview of these files, so I store them in different
folders all within the same 'master' folder. So let's say: master folder is
FolderMaster, and this contains folderA, folderB, folderC. FolderA contains
workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally
folderC contains workbookC1 and workbookC2.

Suppose that this hierarchy might change. Moving eg workbook A2 to folderB.
This would cause a #REF.

So I thought: organise your hierarchy in a 'master workbook' and store all
paths to the individual files in a worksheet. If I link to a specific folder
I do not directly link to the actual folder, but would use INDIRECT and grap
the filepath from the masterworkbook. If the path to a workbook change I do
not have to update all individual links, but only change the path in the
masterworkbook. Right?

But how is the big one here. How do you formulate you path to a file? It can
not be the entire file path since that's variable... (sometimes
'hd:User1:....'; sometimes 'HD:User2'...")

 

[macropod]

Hi dakke,

If you're going to make a hierarchy change, open both the source and target workbooks (with the source workbook in the old folder),
then use File|Save As to save to source workbook to the new folder. That will update the paths in the target workbook. Then delete
(or move) the old source workbook.

Cheers