0

Given a Column A in Excel with multiple cells containing ISBN (book id) values, I want my VBA macro to loop through each of them and, for each one, 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. In this case, I am happy to just grab the title attribute from the first one. But even better would be to make sure it's 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 this error, "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"

I've tried numerous variations to try to isolate the title text but cannot get anything other than this error.

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

Help would be greatly appreciated. I am inexperienced in VBA programming and XML parsing and have been googling/reading on this for more time than I care to admit without making any progress. (There was a previous StackOverflow question on parsing ISBN XML files, but it was for a provider that no longer offers the XML files for free. That code inspired my code, but something was lost in the translation.)

Thanks tons for any help you can offer.

user1883779
  • 61
  • 1
  • 8

1 Answers1

0

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125