Consuming OData with Office VBA - Part III (Excel)

  • Article

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 = "https://ogdi.cloudapp.net/v1/dc/BankLocations/"
    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
    ActiveSheet.Cells.Clear
    ActiveSheet.Cells.ClearFormats
   
    ' 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
    Next
   
    ActiveSheet.Columns("A:B").AutoFit
   
    ' 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.

Enjoy!