View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
Rick S. Rick S. is offline
external usenet poster
 
Posts: 213
Default wrap text with quotes ("")

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