ShareMyPoint ORDINARY KNOWLEDGE : VIVID THOUGHTS

11Nov/089

Excel 2007 creating Hyperlinks programmatically

Posted by Sameer Dhoot under Excel 2007

Today, I had to create a excel repot on SharePoint Libraries (Document and Forms Library) used and URL for each of them. As discussed in the earlier post I had already extracted the information from SharePoint using direct SQL access Enumerating all Document Libraries and Form Libraries in a Site Collection Using SQL

The excel sheet had all the required data but the cell were not hyperlinked even when they were valid url with full path http://servername/sitecollection/subweb/listname/ and there were about 5K+ rows with two columns having the url data. first url was for the site url and the second one was for template associated with the document library or forms library. So played around for some time and could not figure out any built in functionality or routine which could create hyperlink for url.

The manual way of doing it was press "F2" and then enter which then hyperlinked the cell automatically. But I cannot do this for 5k rows manually and for 2 columns each totaling to around 10,000+ modifications. Thought of macro could work and I quickly jumped to write a macro and came with the following code which worked

Sub createHypeLink()  

I = 1
Range("B2").Select
'above statement will slelct the initial start address

While I < 1224
'Please replace this number with # of rows you want to process
'assuming you are starting B2 which is row 2 and column B

    ActiveCell.Hyperlinks.Add Selection, ActiveCell.Text, ActiveCell.Text
    'statement above will create the hyperlink with values in the current cell

    ActiveCell.Offset(0, 1).Select
    'statement above will navigate and select the cell to right by 1 column

    ActiveCell.Hyperlinks.Add Selection, ActiveCell.Text, ActiveCell.Text
    'statement above will create the hyperlink with values in the current cell
    ActiveCell.Offset(1, -1).Select
    'statement above will navigate and select the cell down 1 row and then left by 1 column
    I = I + 1
    'increment the counter by 1 to keep track of rows already processed
Wend

End Sub

So this created the hyperlinks within 2 minutes. I hope this will someone out there. And if you find the post useful then please rate it and provide feedback.

Comments (9) Trackbacks (0)
  1. like it .. but i need a variation.. i need to create hyperlinks with name in column A and web address in column b. Result would read column A as hyperlink. i just want name to appear not address.

  2. Alison,
    Thanks for reading. Well following should answer your question
    Sub Macro2()
    I = 0
    Range(“C2″).Select
    While I < 4
    ActiveCell.Hyperlinks.Add Selection, ActiveCell.Offset(0, -1).Text, , , ActiveCell.Offset(0, -2).Text
    ActiveCell.Offset(1, 0).Select
    I = I + 1
    Wend
    End Sub

  3. Thank you so much for this. It saved me my whole weekend. I had imported several thousand file names and I needed the description in B to contain a hyperlink of the file name that was in A. So, I used the following modification to all me to click on the description in B and it launches the file that resides in the same folder as the spreadsheet:

    Sub Make_URL()

    ‘ Make_URL Macro

    ‘ Keyboard Shortcut: Ctrl+w

    I = 1
    Range(“B2900″).Select

    While I < 1200

    ActiveCell.Hyperlinks.Add Selection, ActiveCell.Offset(0, -1).Text, , , ActiveCell.Offset(0, 0).Text
    ActiveCell.Offset(1, 0).Select

    I = I + 1

    Wend
    End Sub

  4. Great stuff! Just what I was looking for. I had compiled about 3000 links from Twitter and dumped them into Excel and had the same problem. This macro worked like a charm! Thanks for sharing!

  5. many many thanks for posting this solution
    it saved countless hours of manually doing it

    Russ

  6. There is a hyperlink command in Excel 2007:
    =HYPERLINK(B2,A2)

    Where cell B2 will contain the URL and cell A2 will contain the name

  7. can you explain, how make hyperlink when we used vlookup formula? ive made table, which is link of data was put into.

  8. Thank you, Ram. This is it.

    Why didn’t the others find it?

    Why didn’t MS make it easy to find?

  9. This really helped! Thank you :)


Leave a comment



No trackbacks yet.