You’ve just copied a link list template to another site collection, or performed a 2007 to 2010 migration and have realized that the all the urls in the list point to an old site and a mega cut and paste is required
So you think the data sheet can do the trick…. No
And MS Access can help….. Well no
I was surprised at this as well.
This post explains how to dump the list into Excel and run a macro to change the URL and then paste the new links into the list. This is easier than you think by using Excel a tool that you already have.
I’m not an Excel expert and there could be more elegant ways of doing this in Excel
Much of what is explained is in this Excel file: Example file
Dump the list into Excel.
- Click on the Export to Excel from the Ribbon in Excel.
- Create a macro in Excel.
See short video. To see how to create a macro. This could be anything. What is key is to create a macro that you can edit. See figure below:
- Edit the macro. Click the edit button on the dialog.
And paste in the following, macro code.
‘ ReplaceText Macro
‘ Keyboard Shortcut: Ctrl+r
Call ReplaceHyperlinkURL(“co.uk”, “com”)
The code above when run will replace the “co.uk” part of a URL with “com”. This could be anything really.
- Run the macro from the dialog box
All the cells will be searched and if there’s a match, it will replace the text.
Now that the URL’s have been changed.
- Copy and paste the URL’s in Excel back into SharePoint, using the datasheet view.
You just need to paste the URL column.