Plan your holiday meals in Excel

christmas tree

Debra Dalgleish has a couple of meal planners in Excel which you can download. We're going to enhance them with some API goodness!

There are two very useful spreadsheets you can download from her site, if you're planning to host a holiday party.

Visit those articles for screenshots and download links. What we're going to do is make them even better by adding code that looks up recipes via web API, and returns the result to Excel. Now, when you add ingredients to the cooking list, you can look up recipes for cooking suggestions!

We'll use the same technique as found in the YQL post: the XML import feature of Excel 2003 and greater.

The Method

The entire code to call the API and return the response to Excel is as follows:

Sub GetRecipes()
  On Error GoTo ErrorHandler

  Const RangeToImport As String = "$A$10"

  ' delete existing xml map (if any)
  If ActiveWorkbook.XmlMaps.Count > 0 Then
    ActiveWorkbook.XmlMaps.Item(1).Delete
  End If

  ' clear out range to prep for new import
  Range(RangeToImport).CurrentRegion.Cells.Clear

  ' import web XML to destination range
  ActiveWorkbook.XmlImport URL:= _
                           "http://www.recipepuppy.com/api/?q=" & _
    URLEncode(Range("B1").Value) & "&format=xml", ImportMap:=Nothing, _
                           overwrite:=True, Destination:=Range(RangeToImport)

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

As usual, download the URLEncode function to make this work.

You might notice in Debra's workbooks that the DinnerPlan worksheet has dinner items listed multiple times. In order to create a unique list for the recipe lookup dropdown list, I used the code found at Extracting a Unique List.