Consuming OData with Office VBA - Part I

  • Article

This post is the first of a series describing how you can leverage OData information in your Microsoft Office documents and applications using Visual Basic for Applications (VBA).

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.

Option Explicit

' 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
    objXmlHttp.send
   
    If objXmlHttp.Status <> 200 Then
        Err.Raise ODataCannotReadUrlError, "ODataReadUrl", "Unable to get '" & strUrl & "' - status code: " & objXmlHttp.Status
    End If
   
    ' Get the result as text.
    strText = objXmlHttp.responseText
    Set objXmlHttp = Nothing
   
    ' Create a document from the text.
    Set objResult = New MSXML2.DOMDocument60
    objResult.LoadXML strText
    If objResult.parseError.ErrorCode <> 0 Then
        Err.Raise ODataParseError, "ODataReadUrl", "Unable to load '" & strUrl & "' - " & objResult.parseError.reason
    End If
   
    Set ODataReadUrl = objResult
End Function

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("https://ogdi.cloudapp.net/v1/gsa/ConusPerDiemRates2009/")
    ActiveDocument.Content.Font.Name = "Consolas"
    ActiveDocument.Content.Font.Size = 9
    ActiveDocument.Content.Text = objDocument.XML
End Sub

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.

Enjoy!