Exceel Links (Another One !!)
 

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