Consuming OData with Office VBA – Part III (Excel)

This is part three of a series – you should read part I and part II to have the code snippets handy and follow along.

Today we won’t be doing anything special with OData itself, instead we’ll see how the snippets work across different Microsoft Office products. Just start up Excel, paste in the OData import code, and add the following snippet.

Public Sub Sample3_Excel()
    Dim objDocument As MSXML2.DOMDocument60
    Dim objEntries As Collection
    Dim strUrl As String
    ‘ Read the document with data.
    strUrl = “
    Set objDocument = ODataReadUrl(strUrl)
    ‘ Create a collection of dictionaries with name/value pairs.
    Set objEntries = ODataReadFeed(objDocument.DocumentElement)
    ‘ Prepare for updating and clear the document.
    Application.ScreenUpdating = False
    ‘ Build a table for all imported data.
    Dim objEntry As Scripting.Dictionary
    Dim lngRow As Long
    Dim rng As Range
    lngRow = 1
    Set rng = ActiveSheet.Cells
    rng(lngRow, 1) = “Bank Name”
    rng(lngRow, 2) = “Address”
    lngRow = lngRow + 1
    For Each objEntry In objEntries
        rng(lngRow, 1) = objEntry(“name”)
        rng(lngRow, 2) = objEntry(“address”)
        lngRow = lngRow + 1
    ‘ Make the headers bold
    rng(1, 1).Font.Bold = True
    rng(1, 2).Font.Bold = True
    ‘ Now create a PivotTable and count how many addresses
    ‘ each bank has (Excel 2007 syntax).
    Dim cache As PivotCache
    Dim table As PivotTable
    Dim source As Range
    Set source = ActiveSheet.Range(rng(1, 1), rng(lngRow – 1, 2))
    Set cache = ActiveWorkbook.PivotCaches.Create( _
        xlDatabase, source, xlPivotTableVersion14)
    Set table = cache.CreatePivotTable( _
        rng(2, 3), “BankCountTable”, True, xlPivotTableVersion14)
    table.PivotFields(“Bank Name”).Orientation = xlRowField
    table.PivotFields(“Bank Name”).Position = 1
    table.AddDataField table.PivotFields(“Address”), “Address Count”, xlCount
    Application.ScreenUpdating = True
End Sub

This code will import the dataset with bank locations from the District of Columbia, available through the OGDI site. It puts them into the current spreadsheet, then creates a PivotCache and PivotTable over that data, showing you how many locations are on file for each bank. The code to create the pivot tables should work on Excel 2007 up; the syntax to create this on a previous version should be a bit different, but you can try commenting that section out. Or you can record a macro while doing it manually and let Excel tell you how to write the code.


Comments (0)

Skip to main content