wrap text with quotes ("")

[Rick S.]

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\Qadocs\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''s Student]

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

 

[Jim Thomlinson]

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

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

 

[Dale Fye]

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

 

[Rick S.]

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\Qadocs\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

 

[Gary Keramidas]

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 & """)"

 

[Rick S.]

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