Changing path to active directory

[doug.washburn]

Hi,

Similar question to my last one...

I have set up a workbook to link to pages in other workbooks. Problem is, the path in the link is very specific (i.e. C:\monthly reports\April). Each month I create a new folder and copy the master workbbok and the linked workbooks to the new folder so I need the object links in the master book to look in the current folder for it's updates.

Is there a syntax I can change in the object link to tell the master book to look in it's current folder for the linked file?

I tried replacing the syntax from:
=Excel.Sheet.8|'S:\RCS\BU 01\RESP 2380\DEPT 318\SYD Construction\Construction Planning Management\Month End Report 2011\4. April 2011\MASTER TEST\KPIs.xls'!''''
to:
=Excel.Sheet.8|ActiveDocument.Path\KPIs.xls!''''

But that didn't work.

 

[VoG]

You need

ActiveWorkbook.Path

 

[shg]

Are you referring to code or just formulas?

If the latter, then at the beginning of each month, do this:

Make the two old workbooks read-only to avoid an embarassment.

Open both workbooks. Save the KPI workbook to the new folder (the links will follow), then save the other workbook to the new path.

 

[doug.washburn]

That doesn't seem to work. The object still looks like the file I originally linked to in the first folder. It doesn't seem to be updating to the current folder.

 

[shg]

Could you explain the exact steps you took that didn't work?