I am so frustrated right now.
I am trying to place a hyperlink as a formula in a cell but I can not seem
to grasp something of everything I am reading.
I am converting hyperlinks to formula's to prevent loss of links when
copy/paste data to other workbooks. What I get is a link to "C:\Documents
and Settings\user\My
Documents\=HYPERLINK(\\ENGINEERING\PRODUCTION\Qado cs\SPECS\AIR-3277.pdf, AIR
3277)", notice the lack of quotes ("") where required.
The code "hlink.Address = c" only concatenates to the still faulty hyperlink
instead of replacing it completely.

1. How do I get rid of "C:\Documents and Settings\user\My Documents\" from
the string.
2. How do I wrap the new string in quotes? Example shown below. (perhaps
this will answer #1?)
=HYPERLINK("\\ENGINEERING\PRODUCTION\Qadocs\SPECS\ AIR-3277.pdf", "AIR 3277")

"SPECS\AIR-3277.pdf" is from variable "a"
"AIR 3277" is from variable "c"

Below is code Tom Ogilvy wrote for some one and I modified it for my own
insanity. (Thank you Tom, as always).

'==============
Sub ChangeLink()
Dim hlink As Hyperlink
Dim a As String
Dim b As String
Dim c As String
Dim d As String

For Each hlink In ActiveSheet.Hyperlinks
a = hlink.Address
b = hlink.Parent.Address
c = hlink.Name
If Left(a, 6) = "SPECS\" Then
c = "=Hyperlink" & "(\\ENGINEERING\PRODUCTION\Qadocs\" _
& a & ", " & c & ")"
MsgBox c 'for testing
hlink.Address = c
End If
Next
End Sub
'==============

--
Regards

Rick
XP Pro
Office 2007