Categories
Excel 2007

Excel 2007 creating Hyperlinks programmatically

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.

By Sameer Dhoot

Welcome to my blog. I am IT Professional with 10 years of experience across several IT disciplines. I am currently based in Farmington Hills, Michigan, USA. I hope that you find my blog of interest. It is mostly around Microsoft platform including SharePoint, SQL Server, TFS, Windows OS, Virtualization as this is what I am currently working on, but my aim is to write about all of the competencies described in my profile and write contents which I believe can be helpful to broader audience (IT Pros) and may include general computing advice, how-to's, FAQ, Tips and Tricks. I would welcome contributors and critics who would help to build this blog in terms of better contents and usability.

9 replies on “Excel 2007 creating Hyperlinks programmatically”

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.

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

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

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!

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.