Exceel Links (Another One !!)

Hi Everyone (especially Bill Manville),

I have been asked to investgate an issue with Excell and hyperlinks. I will
try and explain the scenraio..

There is an .xls file on a server called contracts.xls . The UNC path to the
doc is \\servername\share\contracts.xls.

Below the share is another folder whichcontains hundreds of scanned
contracts from the sales group. These are in .pdf format.

The .xls has links to each of these .pdf's. The links are using UNC patchs
(as opposed to drive letters).

The aim of the .xls is to allow sales guys to open this .xls, and be able to
quickly loate the relevant contract for that particular customer.

The links were working fine, but all of a sudden some of them have stopped
working. I have tried the suggested fixes in similar threads on this
newsgroup, like adding the unc patch to the HyperLinks Base propertie of the
..xls, and adding a dummy unc path, still no result.

I have noticed that an entry is added to some (not all) of the hyperlinks,
like ../ . Some entries even have up to 3 ../ appended to the strat of the
link.

Can someone please explain to me why this is happening and a possible fix
for it.

The users are using excell 2002 from office XP, however I have been testing
some things on the .xls using Excell from office 2003, would this be the
reason for the problems?

Any help greatly appreciated..

Gary

I don't know if this will work - I have not experienced the problem
myself, but try:

Saving the file containing the links to some other directory, not on
the same server. Do the links then revert to UNC paths?
If so, change the Hyperlink base in File / Properties to a ficticious
server and then save the file back to the correct directory.


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

Hey Bill thanks for that, unfortunetley it did not work.

Saving the file containing the links to some other directory, not on the
same server. Do the links then revert to UNC paths? NO THEY DID NOT REVERT
BACK.

Any other ideas ??

Thanks,
Gary

Gary wrote:
Any other ideas ??

I guess if you made the links using the HYPERLINK worksheet function
Excel would not feel at liberty to mess them up for you.

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

I am having the exact same problem. Yes the hyperlink function solves this
but is much more manual if the file path is long. We are having hyperlink
issues in Office & Word as well but only since switching over to MS Office
2003. Our IT department along with many others have spent hours combing the
web for a solution so if anyone here can suggest something it would be HUGELY
appreciated.

"Bill Manville" wrote:

Gary wrote:
Any other ideas ??

I guess if you made the links using the HYPERLINK worksheet function
Excel would not feel at liberty to mess them up for you.

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

Cydkil wrote:
the hyperlink function solves this
but is much more manual if the file path is long.


Not sure I understand what makes it more manual.
If you have a range of cells containing fixed hyperlinks that you want
to turn into HYPERLINK function calls a macro could be written to do
this. Maybe something like this (which converts all hyperlinks within
the selected range to hyperlink function calls):


Sub MakeHyperlinkFunctions()
Dim R As Range
Dim H As Hyperlink
For Each H In Selection.Hyperlinks
H.Range.Formula = "=HYPERLINK(""" & H.Address & IIf(H.SubAddress
"", "#" & H.SubAddress, "") & """, """ & H.Range.Text & """)"
Next
End Sub


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

It is more manual as we have many links to other file types in different
directories which requires typing or opening an explorer window and copying
and pasting the location from the address bar. Think of a giant Excel file in
directory ABC linking to PDF files in directory LMO and PPT and Word files in
directory XYZ - keep in mind some of these linked files are 4-6 folders deep.

"Bill Manville" wrote:

Cydkil wrote:
the hyperlink function solves this
but is much more manual if the file path is long.


Not sure I understand what makes it more manual.
If you have a range of cells containing fixed hyperlinks that you want
to turn into HYPERLINK function calls a macro could be written to do
this. Maybe something like this (which converts all hyperlinks within
the selected range to hyperlink function calls):


Sub MakeHyperlinkFunctions()
Dim R As Range
Dim H As Hyperlink
For Each H In Selection.Hyperlinks
H.Range.Formula = "=HYPERLINK(""" & H.Address & IIf(H.SubAddress
"", "#" & H.SubAddress, "") & """, """ & H.Range.Text & """)"
Next
End Sub


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

Cydkil wrote:
It is more manual as we have many links to other file types in different
directories which requires typing or opening an explorer window and copying
and pasting the location from the address bar.


If you use my suggested approach in the last message, the only addition to
what you were doing previously to create the links would be: highlighting a
range of cells into which you had added hyperlinks and clicking a button to
run the macro - not a huge overhead I would have thought.

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