Creating URL links using a formula

[Dan Morris]

If
A1 = John
B1 = Bloggs<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
How do I make
C1 = a hyperlink to a file in the format "D:\files\John Bloggs" which I can then click to access John Blogg's files.<o></o>
<o></o>
I got as far as<o></o>
=CONCATENATE("D:\files\",A1," ",B1)<o></o>
but it wouldn't hyperlink to the file structure it had created<o></o>
<o></o>
Can you help?

 

[texasaswang]

Will this work for you? Quick and dirty.

Sub Add_Hyperlink()
Dim Name
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"" "",RC[-1])"
    Name = ActiveCell.Value
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=("D:\files\" & Name)
End Sub

 

[Dan Morris]

Hi texasaswang

That works really well - thanks so much.

Dan

 

[Dan Morris]

Hi Again

Is there a way to make this always CONCATENATE A1 and A2 rather than using the [-2] and [-1] so if new columns are added it's dynamic and doesn't need to re-run the macro?

Also is it possible to make the macro run automatically because if you cut and paste it down a column it just creates the same hyperlink in all the fields (if you see what I mean?)

Thank you. Dan

 

[Scott Huish]

Why not use the HYPERLINK function?

=HYPERLINK("D:\files\"&A1&" "&B1)

 

[texasaswang]

I prefer your solution over mine

 

[Dan Morris]

Hi HOTPEPPER

Thank you for that - it still doesn't quite work, it seems to create a hyperlink for D:\files\ but doesn't seem to add the text from A1 and B1 - is there a space or something wrong in the equation?

Thank you for all your help.

Dan

 

[texasaswang]

Did you add the required spaces?

=HYPERLINK("D:\files\" & A1 & " " & B1)

 

[Dan Morris]

It works, it works, it works.
That's brilliant; thanks both for your brilliant advice and help.
Dan

 

[Dan Morris]

One more question... is there a way to make the result just display "file" rather than the hyperlink below it, ie: it just says "file" but hyperlinks to a file locations as per below?