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.
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!
many many thanks for posting this solution
it saved countless hours of manually doing it
Russ
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
can you explain, how make hyperlink when we used vlookup formula? ive made table, which is link of data was put into.
Thank you, Ram. This is it.
Why didn’t the others find it?
Why didn’t MS make it easy to find?
This really helped! Thank you 🙂