Maximun number of hyperlinks in Excel

[Sam]

I am adding hyperlinks to a excel sheet by writing a macro. After 65502
number of hyperlinks i am not able to add the hyperlinks in excel. Is there
any limitation regarding the number of hyperlinks in a excel sheet? Here is
the macro I have used

Sub HyperLink_Test()

' Local Variables
Dim nWorkSheet As Worksheet
Dim nRange As Range
Dim strAddess As String, strDisplayText As String

' Step 1 : Loop through all worksheets
' 1a : Clear all current hyperlinks
Worksheets("Sheet1").Range("A:Z").ClearContents
' 1b : Create Linked index list

Dim i As Long
Dim j As Long


Dim count As Long

count = 0

For i = 1 To 70000

Set nRange = Worksheets("Sheet1").Range("A:Z").End(xlUp)
If (i > 1) Then
Set nRange = nRange.Offset(i - 1, 0)
End If
For j = 1 To 10



strAddess = "'" & XYZ & "'"
strDisplayText = "HyperLink : "
Worksheets("Sheet Index").Hyperlinks.Add Anchor:=nRange,
Address:="ddd", SubAddress:="", TextToDisplay:="ab"
count = count + 1
If count = 65530 Then
GoTo End1
End If
Set nRange = nRange.Offset(0, 1)

Next j


Next i

End1:



End Sub

 

[Mike H]

Sam,

The specification limits for Excel can be found by following the link.

http://office.microsoft.com/en-us/excel/HP051992911033.aspx

Mike

 

[Bill Sharpe]

Sam,

The specification limits for Excel can be found by following the link.

http://office.microsoft.com/en-us/excel/HP051992911033.aspx

Mike

I don't see hyperlinks mentioned in Mike's reference. I suspect you've
found your limit. Workaround would be to create a second workbook after
you've reached 65,000 hyperlinks.

I've got about 250 hyperlinks in one Excel workbook and thought that was
a lot!

Bill

 

[Sam]

Thanks Bill, yes we have to create a second worksheet, to accommodate the
hyperlinks. But it should be considered as a Microsoft Excel bug, as they
have not mentioned any where there limit on hyperlinks.
What's your opinion regarding this.

 

[Dana DeLouis]

we have to create a second worksheet, to accommodate the hyperlinks.

But it should be considered as a Microsoft Excel bug...

What about this workaround idea...

Suppose all the links are in column A, and they are "text", and not links.
Have a cell, say C1, that holds the only Hyperlink on the sheet.
C1 = "www.dummy.com"

Have a macro that, when a cell is selected in Column A, then the
Hyperlink's address (in C1) is changed to the text of the cell selected,
and the link in C1 is then followed.

This would go on the module sheet for the sheet in question.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub

Dim HL As Hyperlink

Set HL = [C1].Hyperlinks(1)
HL.Address = "http://" & Target.Value
HL.Follow
End Sub

Again, not fancy, but it may give you some ideas..
= = =
HTH
Dana DeLouis