Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Maximun number of hyperlinks in Excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Maximun number of hyperlinks in Excel
Sam,
The specification limits for Excel can be found by following the link. http://office.microsoft.com/en-us/ex...992911033.aspx Mike "Sam" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Maximun number of hyperlinks in Excel
Mike H wrote:
Sam, The specification limits for Excel can be found by following the link. http://office.microsoft.com/en-us/ex...992911033.aspx Mike "Sam" wrote: 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? 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Maximun number of hyperlinks in Excel
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. "Bill Sharpe" wrote: Mike H wrote: Sam, The specification limits for Excel can be found by following the link. http://office.microsoft.com/en-us/ex...992911033.aspx Mike "Sam" wrote: 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? 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Maximun number of hyperlinks in Excel
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 Sam wrote: 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. "Bill Sharpe" wrote: Mike H wrote: Sam, The specification limits for Excel can be found by following the link. http://office.microsoft.com/en-us/ex...992911033.aspx Mike "Sam" wrote: 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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
width at the hlaf maximun peak | Charts and Charting in Excel | |||
Update 2000 Excel hyperlinks to 2003 hyperlinks | Excel Worksheet Functions | |||
How toi turn-off hyperlinks [excel]? Email hyperlinks pop up ! | Excel Discussion (Misc queries) | |||
Maximun number of rows and columns | Excel Discussion (Misc queries) | |||
Excel limitation on number of hyperlinks in worksheet? | Links and Linking in Excel |