Consuming OData with Office VBA - Part I
If you're using the newer programming APIs based on the .NET Framework, then you have plenty of support for OData in the form of the ADO.NET Data Services (soon to be WCF Data Services) client library. Bring your data into memory into strongly-typed objects, work with them locally using LINQ queries, leverage all of the .NET Framework - you get a very high productivity development environment and runtime. Don't take my word for it.
But if you don't, either because you're more familiar with VBA or because you're simply writing a quick one-off script, that doesn't mean that you can't access the increasing data being exposed through OData, both inside corporations and over the Internet.
This series will show you how you can get data with a few lines of code and improve your documents and data analysis tools. Let's get started!
The very first thing to do is to get some data into the application. Let's try doing this in Microsoft Word; fire it up, press Alt+F11 to bring up the code editor, double-click your 'ThisDocument' instance in the top-left Project window, and paste the following code.
' References that need to be added:
' Microsoft XML, v6.0
' Microsoft Scripting Runtime
Const ODataErrorFirst As Long = 100
Const ODataCannotReadUrlError As Long = ODataErrorFirst + 1
Const ODataParseError As Long = ODataErrorFirst + 2
' Given a URL, reads an OData feed or entry into an XML document.
Function ODataReadUrl(ByVal strUrl As String) As MSXML2.DOMDocument60
Dim objXmlHttp As MSXML2.XMLHTTP60
Dim objResult As MSXML2.DOMDocument60
Dim strText As String
' Make a request for the URL.
Set objXmlHttp = New MSXML2.XMLHTTP
objXmlHttp.Open "GET", strUrl, False
If objXmlHttp.Status <> 200 Then
Err.Raise ODataCannotReadUrlError, "ODataReadUrl", "Unable to get '" & strUrl & "' - status code: " & objXmlHttp.Status
' Get the result as text.
strText = objXmlHttp.responseText
Set objXmlHttp = Nothing
' Create a document from the text.
Set objResult = New MSXML2.DOMDocument60
If objResult.parseError.ErrorCode <> 0 Then
Err.Raise ODataParseError, "ODataReadUrl", "Unable to load '" & strUrl & "' - " & objResult.parseError.reason
Set ODataReadUrl = objResult
Next, follow the instructions in the comments and add the references to the two libraries (we'll use the second one in a later post), using the Tools | References window.
This is enough to get us up and running. You can now paste the following code at the bottom and with the cursor on it, press F5 to run. The URL is from the Open Government Data Initiative site, and has 2009 per diem rates for continental U.S., provided by the U.S. General Services Administration.
Public Sub Sample1()
Dim objDocument As MSXML2.DOMDocument60
Set objDocument = ODataReadUrl("http://ogdi.cloudapp.net/v1/gsa/ConusPerDiemRates2009/")
ActiveDocument.Content.Font.Name = "Consolas"
ActiveDocument.Content.Font.Size = 9
ActiveDocument.Content.Text = objDocument.XML
The document will now contain the OData payload in raw form - congratulations, you've taken the first step! We'll refine the way we process the data in a future post, but for now, you've seen how easy it is to get some data into your Office documents.