Gary, Dale thank you!
Jim, I see where I was causing my frustration. I was trying to wrap the
text in the wrong places. I was double quoting in ares that did not require
it, thus causing an error.
'=====Jim's example
c = "=Hyperlink(""\\ENGINEERING\PRODUCTION\Qadocs\ " _
& a & """, """ & c & """)"
'=====
'=====My last attempt (notice quote locations)
c = "=Hyperlink(""\\ENGINEERING\PRODUCTION\Qadocs\ "" _
& a & """, """ & c & ")"""
'=====

Now, about Q #1.
1. How do I get rid of the string "C:\Documents and Settings\user\My
Documents\" from within the hyperlink?
Currently I get this as a hyperlink:
"C:\Documents and Settings\user\My
Documents\=HYPERLINK\\ENGINEERING\PRODUCTION\Qadoc s\SPECS\AIR-3277.pdf, AIR
3277"
As one long string.
I don't want or need "C:\Documents and Settings\user\My Documents\" in the
hyperlink, it is the initial problem.

--
Regards

Rick
XP Pro
Office 2007



"Jim Thomlinson" wrote:

Where you want quotes to show up just use 2 sets of quotes...

c = "=Hyperlink(""\\ENGINEERING\PRODUCTION\Qadocs\ " _
& a & """, """ & c & """)"

--
HTH...

Jim Thomlinson


"Rick S." wrote:

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