Parsing XML file for ISBN title lookup with Excel VBA, Run-time error 91 Object variable or With block variable not set

Question

Given Column A in Excel with multiple cells containing ISBN (book id) values, I want my VBA macro to loop through each cell to parse an online XML file that is unique to that ISBN and put the corresponding book title in Column B.

For example, if A1 contains 1931498717, I should parse this XML and grab the title "Don't think of an elephant! : know your values and frame the debate : the essential guide for progressives" and put that in B1.

Here is a sample of the XML file:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<classify xmlns="http://classify.oclc.org">
  <response code="4"/>
  <!--Classify is a product of OCLC Online Computer Library Center: http://classify.oclc.org-->
  <workCount>2</workCount>
  <start>0</start>
  <maxRecs>25</maxRecs>
  <orderBy>thold desc</orderBy>
  <input type="isbn">1931498717</input>
  <works>
    <work author="Lakoff, George" editions="28" format="Book" holdings="1088" hyr="2014" itemtype="itemtype-book" lyr="2004" owi="796415685" schemes="DDC LCC" title="Don't think of an elephant! : know your values and frame the debate : the essential guide for progressives" wi="796415685"/>
    <work author="Lakoff, George" editions="1" format="Musical score" holdings="1" hyr="2004" itemtype="itemtype-msscr" lyr="2004" owi="4735145535" schemes="DDC" title="Don't think of an elephant! : know your values and frame the debate : the essential guide for progressives" wi="4735145535"/>
  </works>
</classify>

Notice there are two "work" elements. I am happy to grab the title attribute from the first. Better would be to make sure it is the title of a book (format="Book") and not some other format.

Here is my macro code:

Sub ISBN()
Do
    Dim xmlDoc As DOMDocument60
    Set xmlDoc = New DOMDocument60
    xmlDoc.async = False
    xmlDoc.validateOnParse = False

    r = CStr(ActiveCell.Value)
    xmlDoc.Load ("http://classify.oclc.org/classify2/Classify?isbn=" + r + "&summary=true")
  
    ActiveCell.Offset(0, 2).Value = xmlDoc.SelectSingleNode("/classify/works/work[1]").attributes.getNamedItem("title").text
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
End Sub

I get

Run-time error 91 Object variable or With block variable not set

on the line that references "xmlDoc.SelectSingleNode("/classify/works/work[1]").attributes.getNamedItem("title").text"

My Excel file is Microsoft Excel for Microsoft 365, on my laptop.

There is a previous StackOverflow question on parsing ISBN XML files, but it is for a provider that no longer offers the XML files for free. That code inspired my code.

Answer 1

Your XML has a "default" namespace which applies to its contents, so when using xpath you need to create a dummy alias for that namespace and use it in your query.

See https://stackoverflow.com/a/72997440/478884

Eg:

Dim xmlDoc As DOMDocument60, col As Object, el As Object
Set xmlDoc = New DOMDocument60
xmlDoc.async = False
xmlDoc.validateOnParse = False

'set default namespace and alias "xx"
xmlDoc.SetProperty "SelectionNamespaces", _
                   "xmlns:xx='http://classify.oclc.org'"

xmlDoc.Load "http://classify.oclc.org/classify2/Classify?isbn=1931498717&summary=false"
 
Set col = xmlDoc.SelectNodes("/xx:classify/xx:works/xx:work")
'check each `work` for format and title
For Each el In col
    Debug.Print "*****************"
    Debug.Print el.Attributes.getNamedItem("format").Text
    Debug.Print el.Attributes.getNamedItem("title").Text
Next el