Is there a workaround for the 254 character limit in Excel hyperlinks?

If I use the HYPERLINK function with a 500 character URL,
(ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[7],RC[1])")
all I get is #VALUE, but I have found that if I right-click on a cell and
select "Hyperlink" that I can paste the 500 character URL in the address box
and the hyperlink will work

Is there any way to accomplish this via VBA?

Hi there,
I just tryed this and it worked on a 404 character string (a valid
url).

Sub setlink()
Dim r1 As Range
Dim r2 As Range
Set r1 = Sheet1.Range("A1")
Set r2 = Sheet1.Range("A2")
Sheet1.Hyperlinks.Add r1, r2.Value
End Sub

Kind regards,

Bernie Russell

This is very interesting! I tried to do something similar recently, but
couldn't get ti to work, and finally lost interest in the idea. Now, my
interest has been reset, and my expectations have been elevated. Can this be
done with a URL that is 696 characters? Here is a sample of my code (very
simple sample):

Sub setlink()
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Set r1 = Sheet1.Range("A1")
Set r2 = Sheet1.Range("A2")
Set r3 = Sheet1.Range("A3")
Range("A6") = Sheet1.Hyperlinks.Add(r1, r2, r3.Value)
Call Macro1
End Sub

Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Range("A6"), _
Destination:=Range("A10"))
.Name = "Main"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "12"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Well, that's the concept at least. I get an error on this line:
Range("A6") = Sheet1.Hyperlinks.Add(r1, r2, r3.Value)

I still can't quite get my mind around it. Can anyone see what is wrong
here? I would really love to turn this concept into a reality!!

Regards,
Ryan---




--
RyGuy


" wrote:

Hi there,
I just tryed this and it worked on a 404 character string (a valid
url).

Sub setlink()
Dim r1 As Range
Dim r2 As Range
Set r1 = Sheet1.Range("A1")
Set r2 = Sheet1.Range("A2")
Sheet1.Hyperlinks.Add r1, r2.Value
End Sub

Kind regards,

Bernie Russell

Thank you Bernie!!

That was exactly what I was looking for!

" wrote:

Hi there,
I just tryed this and it worked on a 404 character string (a valid
url).

Sub setlink()
Dim r1 As Range
Dim r2 As Range
Set r1 = Sheet1.Range("A1")
Set r2 = Sheet1.Range("A2")
Sheet1.Hyperlinks.Add r1, r2.Value
End Sub

Kind regards,

Bernie Russell