An efficient way to create thousands of hyperlinks with Excel VBA
I have a VBA question for creating hyperlinks for a range of cells.
In my application, I have a data table that lists the ids in one column that I would like to add hyperlinks to to open the corresponding report / page for that ID.
In all of the following cases, all cells are assumed to be populated with numeric identifiers.
I am currently using code very similar to the following (simplified for this example):
Dim r As Range, c As Range
Set r = Range("C1:C60000")
For Each c In r
ActiveSheet.Hyperlinks.add anchor:=c, address:="http://www.url.com?address=" & c.value
Next c
However, as the range increases, the execution time of this code increases longer. In the case where the URL is always the same, the following code is quite fast.
Dim r as Range
set r = Range("A1:A60000")
r.Hyperlinks.add anchor:=r, address:="http://www.url.com"
My question is when urls are different, especially if they contain data from a cell, there is a way that is more efficient than what I am currently doing.
Any help or understanding would be appreciated. Thank,
Sample data (in CSV format and in the form of a screenshot of an Excel spreadsheet), I need to be able to add hyperlinks to the entire column in the format " http://www.urltest.com/sample?id= [ID]", where [ID] is value from cell.
- Number, Successor, Title
- 383087, Me, Fix this problem.
- 403749, you, compiler problem
- 403856, you, a problem with the monitor
- 440912, Me, Three things to fix first.
- 458523, Another person: "You, me, and this problem"
- 476182, You, the Star in the Sky
- 485834, You, Three little bears.
- 499569, you, my keyboard is not working.
- 500552, Me, The mouse is not working.
- 516824, Me, I cannot login
- 523654, other person, too many computer cables.
- 536632, Someone Else, I have a PHP problem
- 556012, Me, I have a problem with VBA
- 561275, You have two problems with C ++
- 569014, Me, My Perl script doesn't work.
- 572658, You, JavaScript is giving me a headache.
- 667911, Me, My headphones are off.
- 704432, I need a software update.
- 721637, Me, My laptop is too slow
- 735784, Me, your server is rejecting my connection.
- 765477, Me, I don't know which port to connect to
- 778808, Me, There is something wrong with my XML
- 788865, Me, The strength of my computer continues to fluctuate.
- 791957, Me, My power panel needs to be replaced.
- 793507, Me, This is necessary before we release the product.
- 794067, Me, There two products don't work well.
- 798445, Me, These things take time.
source to share
The fastest way I can think of is to use a function HYPERLINK
and assign values to all cells in one go :) This will also ENSURE that you should NOT have a loop.
Hyperlink syntax
HYPERLINK (link_location, [friendly_name])
Read about it in Excel Help or on Google.
ADDRESS(ROW(),COLUMN()))
indicates the address of the current cell.
try it
Sub test_simple_diffurl()
Dim r As Range
Set r = Range("C1:C60000")
r.Formula = "=HYPERLINK(""http://www.url.com?address="" & ADDRESS(ROW(),COLUMN()),""Test with "" & ADDRESS(ROW(),COLUMN()))"
End Sub
source to share