Just to share something to add hyperlink in Excel cells.
Background:
Exporting hyperlinks is not supported out of the box by EasyMorph.
I have developed some Excel VBA / macro codes to add hyperlink into cells, it works fine with less than 65582 rows. Here is the main part of the VBA codes:
Dim s As String, xCell As Range
On Error Resume Next
For i = 2 To iRow
If Left(Range("N" & i & ":" & "N" & i).Value, 7) = "http://" Then
s = Range("N" & i & ":" & "N" & i).Value
Set xCell = Range("N" & i & ":" & "N" & i)
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=s, TextToDisplay:=s
End If
Next i
On Error GoTo 0
I have an Excel file with more than 142000 rows, then this VBA / macro fails from row 65582.
I didn't work out a work around in Excel.
Maybe I could approach like this, write 3 different VBA codes to load hyperlinks in 3 steps, 60000 rows a time.
Do you have any good idea?
Thanks for the “tap on the shoulder”, Dmitry. Very solid ideas.
Cambridge This issue would be based on the variables controlling your loop - “i” and “iRow”. How are they declared/dim’ed? Different data types have different max values. i.e., “integer” can only hold values from -32,768 to 32,767, etc.
I suspect, either:
Earlier in your program, something is storing a value only up to 65582 in “iRow”, or
You’re declaring “i” and/or “iRow” using a data type that can only hold a value up to ~65K.
Solutions:
Check earlier in your program to determine what value is being assigned to “iRow” (is something setting it to 65582? and/or
Make sure you’re declaring “i” and “iRow” both as the Long data type, which can hold values up to ~2M.
Hit me up directly if you have any more questions or need more info, Cambridge.
(PS…Just saw something else… You may actually be getting an “overload” error if the variables aren’t being declared properly, but your “On Error Resume Next” statement is suppressing it. Comment out (put an ’ (apostrophe) to the left of ) that line and run it again. You’ll probably see an error that can help you nail down what/where the issue is.)
I define i and iRow as Long. I have modified the codes, and still could not fix it.
Seems the only solution is to create 3 macro / VBA in 3 different xlsm files, and load hyperlinks in 3 batches.
The snapshot is here, it fails again on j=1 and i=5582.
Interesting. I’ve put hyperlinks in sheets, but never that many. Wasn’t aware there was an actual limit to the # in a sheet.
Following the conversation thread, it does mention a max of ~64K per sheet, so would modifying it to put all of the hyperlinks you need on different sheets, within the same workbook, work? (As opposed to creating 3 different workbooks.)
