Automate Excel

I learned from the Amazon Web Services workbook that it’s extremely easy to access a REST Web Service using VBA and import the data to an XML list.

One missing piece after viewing the Amazon workbook was how to also send a login and password with a request if needed. Turns out to be very Simple. To send a login and password just add one additional command WinHttpRequest.SetCredentials (Example code below)

I’ve been trying to improve my XML knowledge lately(or lack thereof) by working on a learning project using the Bloglines Web Services, which allows HTTP requests, and returns XML (OPML, Unread Items, etc).

The learning curve I encountered was instead of simply pushing the XML through a map into cells, I wanted to use MSXML2 (Excel’s XML parser) to gain total control of the data to populate Treeviews, Listboxes, and other UserForm stuff.

The project goal is to to read my bloglines subscriptions in Excel(I’m 60% done), sorta like a Bloglines version of Colo’s EZRSS, then possibly port the effort to OutLook when I’m finished(another learning curve).

Here is example code to pull your Bloglines subscriptions into Excel, it sends your Username and Password for authentication:

'HttpRequest SetCredentials flags.
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0

Private Sub ListSubs()
Dim MyRequest As New WinHttpRequest

    MyRequest.Open "GET", _
    "http://rpc.bloglines.com/listsubs"

    'Set credentials
    MyRequest.SetCredentials "USERNAME", "PASSWORD", _
    HTTPREQUEST_SETCREDENTIALS_FOR_SERVER

    ' Send Request.
    MyRequest.Send

    'And we get this response
    MsgBox MyRequest.ResponseText

End Sub

Notes:
Set a reference to Microsoft WinHTTP Services

Replace USERNAME and PASSWORD with your Bloglines info

This returns the XML to a msgbox for demonstration, you can import it a map or load it to a MSXML2.DOMDocument(I’ve got working code, I’m still experimenting, I’ll follow up). Here is a snippet of the msgbox: