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?
For i = 2 to 20000
exSheet.Hyperlinks.Add exSheet.Range("A" & CStr(i)), exSheet.Cells(i, 2).Value
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?
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).
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.
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.