MSXML usage in Excel for web API request


Thomas Guignard

Hi there

Here's what I'm trying to do. I'm using to retrieve
specific info about books (search by ISBN) in Excel. I have found a way
to use the API by writing this function in VBA:

Function GetISBNSubject(isbn As String)
Dim Loaded
Dim ISBNclean
Dim AccessKey As String
AccessKey = "MYKEY"

' Create an instance of the MSXML Parser
Set MSXML = CreateObject("MSXML.DOMDocument")

' Set MSXML Options
MSXML.Async = False
MSXML.preserveWhiteSpace = False
MSXML.validateOnParse = True
MSXML.resolveExternals = False

ISBNclean = Replace(isbn, "-", "")

' Form the request URL
XMLURL = "" + AccessKey + _
"&results=subjects&index1=isbn&value1=" + ISBNclean

' Issue the request and wait for the response
Loaded = MSXML.Load(XMLURL)

' If the request is loaded successfully, continue
If (Loaded) Then

' Look for the ErrorMsg tag
Set XMLError = MSXML.SelectNodes("//ErrorMsg")

' If it exists, display the message and exit
If XMLError.Length > 0 Then
Debug.Print MSXML.SelectSingleNode("//ErrorMsg").Text
End If

' If there's no error, use XPath to get the subject nodes

SubjectNodes =
GetISBNSubject = SubjectNodes

Debug.Print "The service is not available."
End If

End Function

Here's how a single record from looks like:
<ISBNdb server_time="2008-10-27T15:27:15Z">
<BookList total_results="1" page_size="10" page_number="1"
isbn="1420092286" isbn13="9781420092288">
From Natural Complexity to a World Knowledge Dialogue
From Natural Complexity to a World Knowledge Dialogue: Laying
Foundations for a World Knowledge Dialogue

Frederic Darbellay (Editor), Moira Cockell (Editor), Jerome Billotte
(Editor), Francis Waldvogel (Editor)
<PublisherText publisher_id="efpl_press">EFPL Press</PublisherText>
<Subject subject_id="nonfiction_social_sciences_sociology_general">
Nonfiction -- Social Sciences -- Sociology -- General
subject_id="professional_technical_engineering_general">Professional &
Technical -- Engineering -- General</Subject>
<Subject subject_id="science_general_aaaa0">Science --

Using MSXML.SelectSingleNode, I can access the contents of a single
node, and retreive its text content. But I'd like to access the text
content of all three Subject subnodes, to concatenate them more smartly
than simply getting the Text content of the parent node, as in my example.

I'd like to do something like

Do While SubjectNodes.nextChild
MyString = MyString + "; " + SubjectNodes.Text

But I have no idea of which functions I'm supposed to use to navigate
through the DOM model created by CreateObject("MSXML.DOMDocument").
Since I'm only a very casual user of VBA, I have lots of trouble
understanding which functions I can use on help pages like
and which I can't.

If someone could point out where I could find the documentation on the
functions I'm using here, I might be able to solve the problem myself.
As of now, I'm getting really confused...

Thanks for your help!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question