Quotation Marks

[Brettjg]

Blimey, I'll bet I'm not the first to trip up on these! After two hours, I
give in and defer to your superiority!

Using a subroutine I want to create a formula in (say) cell A1. I want the
formula in the cell to read:
=HYPERLINK("C:\1. ACTIVE CLIENTS\Duke, E","Duke, E")
but the thing is that Duke, E is a variable.

This is set by coding
client_folder = surname & ", " & initial

I know that this works:
Cells(pasterow, pastecol).FormulaR1C1 = "=HYPERLINK(""C:\1. ACTIVE
CLIENTS\Duke, E"",""Duke, E"")"

so now I need the code to replace Duke, E with client_folder. Help
me, pleeeeeeeeeeeease!

 

[Bob Phillips]

=HYPERLINK("C:\1. ACTIVE CLIENTS\"&client_folder,client_folder)

 

[Nigel]

=HYPERLINK("C:\1. ACTIVE CLIENTS\" & Duke & "," & E & "," & Duke & "," & E
& ")"

But better use the created client_folder name string and then use that....

=HYPERLINK("C:\1. ACTIVE CLIENTS\" & client_folder & "," & client_folder &
")"

 

[Brettjg]

Hi Nigel
this doen't work (from Bob)
Cells(pasterow, pastecol).FormulaR1C1 = "=HYPERLINK("C:\1. ACTIVE
CLIENTS\"&client_folder,client_folder)"

and this doesn't work
Cells(pasterow, pastecol).FormulaR1C1 = "=HYPERLINK("C:\1. ACTIVE
CLIENTS\" & client_folder & "," & client_folder & ")""

Brett

 

[Brettjg]

Hi Bob, I tried that and also Nigel's sugestion. These are what I tried, but
neither works:

Cells(pasterow, pastecol).FormulaR1C1 = "=HYPERLINK("C:\1. ACTIVE
CLIENTS\"&client_folder,client_folder)"

but i inserted some spaces to make it:
Cells(pasterow, pastecol).FormulaR1C1 = "=HYPERLINK("C:\1. ACTIVE
CLIENTS\" & client_folder, client_folder)"
neither works

From Nigel:
Cells(pasterow, pastecol).FormulaR1C1 = "=HYPERLINK("C:\1. ACTIVE
CLIENTS\" & client_folder & "," & client_folder & ")""
doesn't work.

Brett

 

[Jim Cone]

Commas in a folder name?
'--
Sub wtf()
Dim strFirst As String
Dim strClientFolder As String
Dim surname As String
Dim initial As String

surname = "Duke"
initial = "E"
strClientFolder = surname & ", " & initial
strFirst = "C:\1. ACTIVE CLIENTS\" & strClientFolder

Range("B10").Value = "=Hyperlink(""" & strFirst & """, """ & strClientFolder & """)"
End Sub
--
Jim Cone
Portland, Oregon USA



"Brettjg"
<[email protected]>
wrote in message
Blimey, I'll bet I'm not the first to trip up on these! After two hours, I
give in and defer to your superiority!

Using a subroutine I want to create a formula in (say) cell A1. I want the
formula in the cell to read:
=HYPERLINK("C:\1. ACTIVE CLIENTS\Duke, E","Duke, E")
but the thing is that Duke, E is a variable.

This is set by coding
client_folder = surname & ", " & initial

I know that this works:
Cells(pasterow, pastecol).FormulaR1C1 = "=HYPERLINK(""C:\1. ACTIVE
CLIENTS\Duke, E"",""Duke, E"")"

so now I need the code to replace Duke, E with client_folder. Help
me, pleeeeeeeeeeeease!

 

[Jim Cone]

Further...
It may be easier to understand what was done when
using a character code instead of the quotation marks.

Range("B11").Value = _
"=Hyperlink(" & Chr$(34) & strFirst & Chr$(34) & ", " & Chr$(34) & strClientFolder & Chr$(34) & ")"

 

[Brettjg]

Hi Jim, that's it! Funny name for a sub though. When I'm testing them I like
the names to start with A_ so they head up the list. Therefore I renamed your
sub Aaah_wtf.

You've probably guessed that this is my solution to the previous problem
with opening folders.

Why don't you like commas and ful stops in folder & file names? I've never
had any problems with them. My Excel crashes are something wierdo to do with
printing either normally or with PDFCreator (every so often it freezes at a
random printjob), but I seem to have coded my way around that today with a
few checks and balances.

Thanks for you help Jim, it's late over here, but wtf. Brett.