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

You have a right to be frustrated! I always get so confused about embedding
double quotes that I use:

Dim dq as String
dq=Chr(34)

and then use dq when building the string
--
Gary''s Student - gsnu200756


"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

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

I actually have a function (mostly used in Access) that does this.

Public Function fnQuotes(TextToQuote As Variant, _
Optional Character As Variant = Null) As
String

'Accepts a variant to handle NULLs
'Accepts an optional Character string as the delimiter to handle ", ',
or #
If IsNull(Character) Then Character = Chr$(34)
fnQuotes = Character & TextToQuote & Character

End Function

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"Gary''s Student" wrote:

You have a right to be frustrated! I always get so confused about embedding
double quotes that I use:

Dim dq as String
dq=Chr(34)

and then use dq when building the string
--
Gary''s Student - gsnu200756


"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

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

if you have your code correct and just want to split the line, you can download
mztools from mztools.com and use the spit lines feature. a lot of other useful
features in it, too.

if the line isn't long enough to split, you can just enter a tab or spaces in
front and then run it.


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

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

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

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

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

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

--


Gary


"Rick S." wrote in message
...
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

I do not think I need to "split the line", per se, I need to delete the
existing hyperlink and create a new formula based hyperlink. But deleting
the hyperlink ends the use of variables used in the code, thus, causing the
code to crash and my hair to fall out.

--
Regards

Rick
XP Pro
Office 2007



"Gary Keramidas" wrote:

if you have your code correct and just want to split the line, you can download
mztools from mztools.com and use the spit lines feature. a lot of other useful
features in it, too.

if the line isn't long enough to split, you can just enter a tab or spaces in
front and then run it.


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

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

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

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

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

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

--


Gary


"Rick S." wrote in message
...
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