Path of Cell reference changes upon Copying

[njskatchmo]

I am creating a shared file and I want it to to be as "robust" as possible.

The file is kept on a shared drive G:\. When you hit file>save as, and save it to C:\, it works fine.

However, if you copy the file from G:\ to C:\ it changes all the paths of the references to other workbooks.

For instance,
='G:\Deptdata\Place Vacancy and Rents Files in here\[ofrc_dts.csv]ofrc_dts'!E5

Changes to ='C:\Documents and Settings\Desktop\Place Vacancy and Rents Files in here\[ofrc_dts.csv]ofrc_dts'!E5

(It looks for a folder in the same folder as the excel workbook.)

How can I prevent the directory from ever changing?

 

[njskatchmo]

It's not an issue of "update links on save" because I've unchecked that.

 

[njskatchmo]

top it off

 

[pbornemeier]

Possibly using Indirect
=Indirect("'G:\Deptdata\Place Vacancy and Rents Files in here\[ofrc_dts.csv]ofrc_dts'!E5")

 

[njskatchmo]

This works perfectly, but if the ofrc_dts file is not open when I open the excel file, excel pretty much locks up. Is there anyway to prevent it from attempting to update these links?

 

[pbornemeier]

From Excel 2003 help (excerpt from topic: Control when links are updated

- Don't ask to update links for this workbook, and let me control whether links are updated
Warning This option affects all users of the workbook. If you choose to not update links, and not to prompt, users of the workbook will not know the data is out of date.

On Edit menu, click Links.
Click Startup Prompt.
Select the option you want.

Note You will still be notified if there are any broken links.

 

[PCRIDE]

maybe type in the network path \\networkshare

Also I have noticed moving and saving the file else where then returning to the original path, excel does "find" the sheet and reconnect it.

Its risky though...

 

[njskatchmo]

From Excel 2003 help (excerpt from topic: Control when links are updated

- Don't ask to update links for this workbook, and let me control whether links are updated
Warning This option affects all users of the workbook. If you choose to not update links, and not to prompt, users of the workbook will not know the data is out of date.

On Edit menu, click Links.
Click Startup Prompt.
Select the option you want.

Note You will still be notified if there are any broken links.

I've tried this, however, because it's an indirect function, excel doesn't seem to consider this a link, so telling it not to update does nothing.

I don't quite understand your idea PCRIDE.

 

[pbornemeier]

You might be able to use this method to pull data from a closed workbook:
http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/