Hyperlink concatenated exceed 255 characters

[thomas854]

Hello guys,

First thank you for this great forum and all informations !

Well, I will explain my issue.


I need to open a Hyperlink concatenated in a cell (by example A1) who exceed 255 characters.
I tried a lot of VBA, macros and all have an issue...

The cell must be clickable and show a hyperlink name like "CLICK HERE" and need to open the very long url in the default browser.

Please I need your help.

 

[JazzSP8]

Welcome to the Forum

Try putting the URL together in the formula instead of trying to do it beforehand.

So, if you have it in two or three parts, something like;

=HYPERLINK(A1&A2&A3,"Click here")

 

[thomas854]

Not working with url who exceed 255 characters...
Only VBA script can do it, anyone please?

 

[admiral100]

Replace the link with yours

Sub LongHyperlink()




ActiveCell.Hyperlinks.Add Range("A1"), "http://www.thelongestlistofthelongeststuffatthelongestdomainnameatlonglast.com/wearejustdoingthistobestupidnowsincethiscangoonforeverandeverandeverbu****tilllookskindaneatinthebrowsereventhoughitsabigwasteoftimeandenergyandhasnorealpointbutwehadtodoitanyways.html", TextToDisplay:="CLICK HERE"




End Sub

 

[thomas854]

Thanks for the VBA code, unfortunately my URLs are in a cell range, how to add it in your VBA script please?
The best way will be if the VBA convert all URl to clickaque links... and open it in internet explorer.

Thanks.

 

[thomas854]

Sorry I made a typo but I am not able to edit the post...

I mean to let me open the link next in internet explorer...

 

[admiral100]

If I understood you correctly (if not please attach file)

A1 to D1 contain the Url's (change as needed)


Sub OPenHyperlinks()


Dim rng As Range
Set rng = Application.Range("A1:D1")
Dim cel As Range
Dim HP As Hyperlink




For Each cel In rng
If Not cel.Value Like "CLICK*" Then
ActiveSheet.Hyperlinks.Add Anchor:=cel, Address:=cel, TextToDisplay:="CLICK HERE" & cel.Column


End If
Next cel




For Each HP In ActiveSheet.Hyperlinks
If HP.Address Like "http*" Then
HP.Range.Offset(1, 0).Value = HP.Address
ActiveWorkbook.FollowHyperlink HP.Address
End If


Next




End Sub

 

[thomas854]

Thanks admiral100 for your reply!


I will explain better :

The Hyperlinks are in the column "H" (from "H4" to "H299")

*By example for line 10 :
The link is created in the cell "H10" and depend of data entered in cells A10+B10+C10+D10
As the Hyperlink exceed 255 characters it is not clickable and can not be replaced by a hyperlink name.

So I just need the Hyperlink in the column "H" become clickable automatically when data are entered in the column "E" as all hyperlink informations have been entered in cells before (A10+B10+C10+D10 for this example) and show a Hyperlink name instead the full hyperlink text.

As I can not put public the file here, I sent you the Excel file by PM.


Thanks for all your help.

 

[admiral100]

Hi,

I checked the formula on your file and it seems to work except for links that have spaces and therefore the URL is not valid...


So I suggest that you replace the formula in column H with the following formula:
H4 =IF (A4="","",SUBSTITUTE (L4 & M4 & N4 & O4 & P4 & Q4 & R4 & S4 & T4 & U4," ",""))


Vba code:

Sub hyp()


Dim rng As Range
Set rng = Application.Range("H4:H299")
Dim cel As Range
Dim i As Integer
i = 1


For Each cel In rng

ActiveSheet.Hyperlinks.Add Anchor:=cel, Address:=cel.Value, ScreenTip:="My Link", TextToDisplay:="Click Here" & i

i = i + 1


Next


End Sub

 

[thomas854]

Thank you very much!


Now 2 issues :

The first one is all needed spaces in the URL are removed so now my Email look like this : ThefirstoneisallneededspacesintheURLareremovedsonowmyEmaillooklikethis

Then the Hyperlink is working but don't show the name, I get again the full Hyperlink URL...


Thanks for your future help!