Programmatically adding hyperlinks

pootle flump · #1 (**permalink**) 05-26-06, 08:42

Hi

I have a database that exports the contents of an ADO recordset to excel for analysis. There are two columns concerned with this post: [Text] and [Link]. The values of the cells in the [Link] column are the urls I wish to add to the corresponding cell in the [Text] column. The values in [Link] are directly derived from [Text].

I have code that does this quite merrily in a loop and worked super in proof-of-concept testing. My problem is that the export frequently includes several tens of thousands of rows. Assigning the hyperlink to the cell in the text column row by row with this sort of dataset is unworkably slow (measured in minutes rather than seconds). Some of the values in [Link] are repetitious and the data happens to be ordered on a column that should keep these values grouped fairly well. I could assign the hyperlink to a range of cells in the column but I still don't expect this to be fast enough.

Have I missed something? Is there a quicker, better way of achieving this?

Code below:

Code:

For i = 2 to 20000
 
     exSheet.Hyperlinks.Add exSheet.Range("A" & CStr(i)), exSheet.Cells(i, 2).Value
 
Next i

TIA

Fazza · #2 (**permalink**) 05-28-06, 19:58

Hi PF,

I don't think you've missed anything. (You might gain a tiny fraction of a speed improvement by putting the exSheet reference outside your loop, or some other code optimisation.) Though, I suspect to create so many hyperlinks will take a long time however it is coded.

What about a different approach - such as, don't use hyperlinks but an event like double click (to exceute some code) to achieve the same thing?

Fazza

pootle flump · #3 (**permalink**) 05-31-06, 05:43

Hi Fazza

Thanks for the thoughts. I had a play with adding the event however I came up against this: http://support.microsoft.com/kb/q282830/ Altering the set up of the Office installations isn't really an option. I don't want to embed the code in the template I write to for a few reasons (not least of which is the macro warning dialog).

Thanks again

norie · #4 (**permalink**) 05-31-06, 09:20

Why not try using the HYPERLINK worksheet function?

You could easily write code that inserted that into a range without having to loop.

It might not quite give you exactly the desired result though.

pootle flump · #5 (**permalink**) 06-02-06, 03:53

I double checked the code. Looping through the 40K+ lines took no time at all. It was the writing the hyperlinks that took up the time. I decided to try the "write a range of hyperlinks at a time rather than one cell at a time" method and this improved things but not enough.

However -

Quote:

Originally Posted by norie

Why not try using the HYPERLINK worksheet function?

Because I didn't know there was one

I will have a play.

pootle flump · #6 (**permalink**) 06-02-06, 04:37

Nice one Norie - that was exactly what I was after.

Bad news - the CopyFromRecordset method affects the Cells' values not formula so I ended up with "=HYPERLINK("http://www.dbforums.com", "Some Text")" displayed rather than a link.

Good news - just tested some code:

Code:

Me.Range(Me.Cells(2, 2), Me.Cells(334, 2)).Formula = Me.Range(Me.Cells(2, 2), Me.Cells(334, 2)).Value

which works just fine.

Thank you very much for your help

Internet Services

Web Development

Digital Marketing

Consumer Tech