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 (Update: this service no longer exists), 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
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:
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!