Monday, August 24, 2009

Fixing static links upon migration from SharePoint 2003 to 2007

In many cases, Sharepoint sites contain hard coded links pointing to other pages in the site collection. Upon migration from WSS V2 to V3 (for example) you may have lists with many such links requiring changes, usually in their prefix.

Fortunately, Excel can help out with this task.

One way to make the changes would be:
  1. Export the list from SharePoint to Excel.
  2. Apply a formula that extracts the URL from its 'friendly name', which requires using a UDF (User Defined Function) macro.
  3. Apply the 'Copy' and 'Paste special' as values to replace the URL formulas with values. Use 'Replace all' to get the new URLs.
    **It is assumed that you know how to do that.
  4. Apply the 'HYPERLINK' formula to create the new links.
  5. Copy the new links back to the SharePoint list.
Export the list from SharePoint to Excel
  • Navigate to the list in SharePoint.
  • Change the list to 'Edit in a Datasheet' (in Actions)
  • Export it to Excel (in Actions)
Extract the original URLs
In VBA, add the following function:
Function MyLinkText(pRange As Range) As String
Dim String1 As String
Dim String2 As String
If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If
String1 = pRange.Hyperlinks(1).Address
String2 = pRange.Hyperlinks(1).SubAddress
If String2 <> "" Then
String1 = "[" & String1 & "]" & String2
End If
MyLinkText = String1
End Function
Use the function we have just defined to extract the URL only. (let's say your link's column is C and we are in cell D2):
=MyLinkText(C2)
Change the prefixes to the new URLs
Convert the formulas in column D to values and replace their prefix to comply with the names in the new site collection. This is done by copy, paste special / values, and then replace all.
Create the new links
Now you have the friendly name in C and the correct URL in D. In E2 Apply the formula:
=HYPERLINK(MyLinkText(D2),C2)
This will create the desired links in column E.
Copy back to SharePoint
Copy the values in E. (Make sure you have the same numbers of rows as in the SharePoint Datasheet and you didn't change the order).
Paste into the first row in the link's column in SharePoint.

No comments:

Post a Comment

Enter your comment here