It’s end of the fiscal year for Microsoft right now, which means there’s an absurd amount of reports, filling out forms, updating Siebel, and general grunt work that has to be done. One of the things I needed to do was to submit a list of resources into a SharePoint site. That list of resources was provided to me in an email as hyperlinks with text, kind of like this:
|Microsoft home site|
|MSDN Developers site|
|Silverlight main site|
|Windows Client Community Site|
|Official ASP.NET site|
I needed to take this data and shove it into a SharePoint list. There were over 50 entries, and the thought of right-click, copy hyperlink, paste into SharePoint, copy text, paste into SharePoint, repeat 50 times seemed ridiculous. Instead, I was able to quickly leverage an Excel macro to cut this work significantly.
- Copy the list into Excel into column B.
- On the View menu, click Macros / Record macro…”. Give it a shortcut key like SHIFT+H.
- Copy the text from one column into another.
- On the View menu, click Macros / Stop Recording.
- On the View menu, click Macros / View Macros
- Select the macro that you just created and choose edit.
- Edit the macro to look like this:
Sub Macro6() ' ' Macro6 Macro ' ' Keyboard Shortcut: Ctrl+SHIFT+A ' ActiveCell.Select Dim hlink As String hlink = Selection.Hyperlinks.Item(1).Address Selection.Hyperlinks.Delete ActiveCell.Offset(0, -1).Value = hlink End Sub
- Close the macro editor.
- Put the cursor into a cell containing the hyperlinked text and press Ctrl+SHIFT+A.
The result is that the hyperlink is copied to the column to the left, and the hyperlink is removed from the text:
|http://www.microsoft.com||Microsoft home site|
|http://msdn.microsoft.com||MSDN Developers site|
|http://silverlight.net||Silverlight main site|
|http://windowsclient.net||Windows Client Community Site|
|http://www.asp.net||Official ASP.NET site|
Now, I have the information in Excel… how can I get this into SharePoint? Go to the list that you want the data to be inserted into. In the Actions menu, choose “Open with Access”. That will open the table with Access. Make sure you choose to open the table as a linked table.
Last, I just select the data from the Excel sheet and paste into the Access table.
I admit, I have never programmed Excel macros before, and I don’t plan on trying to make a career of it. But with just a few lines of code, I can save myself an asinine amount of work.