Microsoft Collaboration, CTO viewpoint, and New York

The blog for Office 365, Power Apps, Flow, SharePoint, cloud, ….. plus a little NYC

Changing the URL in a Links list: How To

Written by Peter Ward on October 23, 2012 – 10:25 am -

Problem:

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

Steps:

Dump the list into Excel.

  1. Click on the Export to Excel from the Ribbon in Excel.

 

  1. 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:

 

 

 

  1. Edit the macro. Click the edit button on the dialog.

And paste in the following, macro code.

Sub ReplaceText()

‘ ReplaceText Macro

‘ Keyboard Shortcut: Ctrl+r

 

Call ReplaceHyperlinkURL(“co.uk”, “com”)

End Sub

The code above when run will replace the “co.uk” part of a URL with “com”. This could be anything really.

  1. 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.

 

  1. Copy and paste the URL’s in Excel back into SharePoint, using the datasheet view.

You just need to paste the URL column.

Tags:

Posted in Document Management and Lists and Microsoft and Tools.