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:
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.
' Macro6 Macro
' Keyboard Shortcut: Ctrl+SHIFT+A
Dim hlink As String
hlink = Selection.Hyperlinks.Item(1).Address
ActiveCell.Offset(0, -1).Value = hlink
The result is that the hyperlink is copied to the column to the left, and the hyperlink is removed from the text:
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.
PingBack from http://stevepietrek.com/2008/06/26/links-6262008/
Another good choice is to find a text editor that supports regular expressions, and process the HTML with that.
Simply replace something like:
Try that with the HTML: <a href="www.google.com">Google</a>
at www.regextester.com and you'll see what I mean. Although the Regex is pretty tight on the format of link that it expects to match against.
(I use textpad for this, although the regex syntax is slightly different. I've saved friends weeks of work by doing that.)