I'm posting this in the links group too, but there's not much traffic there
so I thought I'd try my luck here too.

I have set up an Excel sheet in which one of the columns contains a link to
different excel sheet; Rows contain information about individual sampels and
for each row, there's a link to the corresponding analysis worksheet. My
problem is that multiple users access this sheet, and when they save any
changes the links get updated to a path that is not valid for me and others
users. I know how to disable this behavior on my own copy of Excel (under
Tools .. Options .. General .. Web Options) but how can I globally disable
it for the sheet? I've tried UNC paths and drive letter paths, no dice. Is
there a way to lock just the cells that have the links in them? A
file-specific disabling of the update? Any help much appreciated!

D

An AutoRun macro that disables the "update links on save" works for us.

Dennis Benjamin wrote:

I'm posting this in the links group too, but there's not much traffic there
so I thought I'd try my luck here too.

I have set up an Excel sheet in which one of the columns contains a link to
different excel sheet; Rows contain information about individual sampels and
for each row, there's a link to the corresponding analysis worksheet. My
problem is that multiple users access this sheet, and when they save any
changes the links get updated to a path that is not valid for me and others
users. I know how to disable this behavior on my own copy of Excel (under
Tools .. Options .. General .. Web Options) but how can I globally disable
it for the sheet? I've tried UNC paths and drive letter paths, no dice. Is
there a way to lock just the cells that have the links in them? A
file-specific disabling of the update? Any help much appreciated!

D

Thanks for your reply Bob. I got another helpful suggestion in the links
group that may interest you:

From: "Bill Manville"
Subject: How to disable automatic updating
Date: Thursday, January 10, 2008 3:46 PM

You're talking about hyperlinks I guess.

By default, if the destination file of the link is addressable relative
to the folder in which the workbook is saved then Excel will try to
turn the links into relative links. This has the advantage that if
different users access the file by different drive letter mappings or
if you move the files as a set then the links will still work. The
down-side is that if you move the workbook the links will no longer
work.

One solution (before the links get changed to being relative!) is to
set the hyperlink base address for the workbook to a non-existent path
(e.g. \\NoServer\Nodirectory ). If the workbook has such a hyperlink
base address for the workbook then the links will not be changed (as
relative links would be interpreted as starting from the hyperlink base
address).

To set the hyperlink base, in Excel 2003 or earlier (you didn't say
which version, it always helps):

- Open the workbook; ensure the hyperlinks are addressing the correct
locations; File Properties Summary Hyperlink Base : enter some
non-existent path.

An alternative solution is to specify the hyperlinks using the
HYPERLINK worksheet function.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



"Bob I" wrote in message
...
An AutoRun macro that disables the "update links on save" works for us.

Dennis Benjamin wrote:

I'm posting this in the links group too, but there's not much traffic
there so I thought I'd try my luck here too.

I have set up an Excel sheet in which one of the columns contains a link
to different excel sheet; Rows contain information about individual
sampels and for each row, there's a link to the corresponding analysis
worksheet. My problem is that multiple users access this sheet, and when
they save any changes the links get updated to a path that is not valid
for me and others users. I know how to disable this behavior on my own
copy of Excel (under Tools .. Options .. General .. Web Options) but how
can I globally disable it for the sheet? I've tried UNC paths and drive
letter paths, no dice. Is there a way to lock just the cells that have
the links in them? A file-specific disabling of the update? Any help much
appreciated!

D

Thank you for the info.

Dennis Benjamin wrote:

Thanks for your reply Bob. I got another helpful suggestion in the links
group that may interest you:

From: "Bill Manville"
Subject: How to disable automatic updating
Date: Thursday, January 10, 2008 3:46 PM

You're talking about hyperlinks I guess.

By default, if the destination file of the link is addressable relative
to the folder in which the workbook is saved then Excel will try to
turn the links into relative links. This has the advantage that if
different users access the file by different drive letter mappings or
if you move the files as a set then the links will still work. The
down-side is that if you move the workbook the links will no longer
work.

One solution (before the links get changed to being relative!) is to
set the hyperlink base address for the workbook to a non-existent path
(e.g. \\NoServer\Nodirectory ). If the workbook has such a hyperlink
base address for the workbook then the links will not be changed (as
relative links would be interpreted as starting from the hyperlink base
address).

To set the hyperlink base, in Excel 2003 or earlier (you didn't say
which version, it always helps):

- Open the workbook; ensure the hyperlinks are addressing the correct
locations; File Properties Summary Hyperlink Base : enter some
non-existent path.

An alternative solution is to specify the hyperlinks using the
HYPERLINK worksheet function.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



"Bob I" wrote in message
...

An AutoRun macro that disables the "update links on save" works for us.

Dennis Benjamin wrote:


I'm posting this in the links group too, but there's not much traffic
there so I thought I'd try my luck here too.

I have set up an Excel sheet in which one of the columns contains a link
to different excel sheet; Rows contain information about individual
sampels and for each row, there's a link to the corresponding analysis
worksheet. My problem is that multiple users access this sheet, and when
they save any changes the links get updated to a path that is not valid
for me and others users. I know how to disable this behavior on my own
copy of Excel (under Tools .. Options .. General .. Web Options) but how
can I globally disable it for the sheet? I've tried UNC paths and drive
letter paths, no dice. Is there a way to lock just the cells that have
the links in them? A file-specific disabling of the update? Any help much
appreciated!

D