Kirk Evans Blog

.NET From a Markup Perspective

Excel Macros to Reduce Repetitive Tasks

@kaevans

Excel Macros to Reduce Repetitive Tasks

Rate This
  • Comments 2

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.

  1. Copy the list into Excel into column B.
  2. On the View menu, click Macros / Record macro...".  Give it a shortcut key like SHIFT+H.
  3. Copy the text from one column into another.
  4. On the View menu, click Macros / Stop Recording.
  5. On the View menu, click Macros / View Macros
  6. Select the macro that you just created and choose edit.
  7. 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
  8. Close the macro editor. 
  9. 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. 

Done.

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:

    <a href="([^"]*)"[^>]*>([^<]+)</a>

    with:

    $1, $2

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

Page 1 of 1 (2 items)
Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
Translate This Page
Search
Archive
Archives