Managing Links in WSS and MOSS Using Excel

Frequently, I am asked to check a page for links. Some pages have hundreds of links on them, links that appear, mostly, in the dreaded Content Editor Web Part. To properly manage those links, and deal with them on a massive basis, I need to extract them and manipulate them in batches.

First thing I do is copy them into Excel so that all links are in one column. I manually copied and pasted them into Excel from the Content Editor Web Part. Sometimes, I can get a whole lot of them, sometimes, just one or two at a time. If you are lucky, you will have a link list, that you can just open in datasheet view or export to Spreadsheet.

Put titles in your column in row 1:

• A: Bad Link
• B: Link Text
• C: Link URL
• D: New Link
• E: Category

Now we have to deconstruct the URL from the title. There isn't a formula for this, although there should be, so we have to develop VBA code in Excel. Don't worry, it will be easy:

1. Place the cursor into an empty cell
2. Click Record Macro from the Developer Tool ribbon
3. Click OK to name the macro
4. Type 'asdf' and press Enter
5. Click Stop Recording from the Developer Tool ribbon
6. Click Visual Basic from the Developer Tool ribbon
7. Select Module1
8. On the text area, (big area with the code in it) press [CTRL]+A to select all.
9. Press [DELETE]
10. Copy this code into the empty space:
    
    Function GetAddress(HyperlinkCell As Range)
    GetAddress =
    Replace(HyperlinkCell.Hyperlinks(1).Address, "mailto:","")
    End Function
    
    Function GetText(HyperlinkCell As Range)
    GetText=HyperlinkCell.Hyperlinks(1).TextToDisplay
    End Function
    
    
    
11. Close the Visual Basic window
12. Place this formula into Cell B2: =GetText(a2)
13. Place this formula into Cell C2: =GetAddress(a2)
14. Place this formula into Cell D2: =hyperlink(c2,b2)
15. Fill out Column E with the category name for your link.

Now, you have all of the Link Titles and Link URLs in separate columns.

Now that you have the URL exposed, you can manage them. One thing this is really good for is getting rid of that pesky CTID in your URLs. You can also replace the space character with %20 so that the URLs work correctly when they used used elsewhere.

We have some alternate access mappings, so in order for these links to work properly for the proper audience, I use this method to quickly go through the links and change them to the correct 'field use' domain names.