Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Programmatically adding hyperlinks

    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
    Last edited by pootle flump; 05-26-06 at 09:53.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    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

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Last edited by pootle flump; 06-02-06 at 04:57.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •