T
Thomas Guignard
Hi there
Here's what I'm trying to do. I'm using http://isbndb.com 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 MSXML
Dim XMLURL
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 = "http://isbndb.com/api/books.xml?access_key=" + 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 =
MSXML.SelectSingleNode("ISBNdb/BookList/BookData/Subjects").Text
GetISBNSubject = SubjectNodes
Else
Debug.Print "The service is not available."
End If
End Function
Here's how a single record from isbn.net looks like:
<ISBNdb server_time="2008-10-27T15:27:15Z">
<BookList total_results="1" page_size="10" page_number="1"
shown_results="1">
<BookData
book_id="from_natural_complexity_to_a_world_knowledge_dialogue"
isbn="1420092286" isbn13="9781420092288">
<Title>
From Natural Complexity to a World Knowledge Dialogue
</Title>
<TitleLong>
From Natural Complexity to a World Knowledge Dialogue: Laying
Foundations for a World Knowledge Dialogue
</TitleLong>
<AuthorsText>
Frederic Darbellay (Editor), Moira Cockell (Editor), Jerome Billotte
(Editor), Francis Waldvogel (Editor)
</AuthorsText>
<PublisherText publisher_id="efpl_press">EFPL Press</PublisherText>
<Subjects>
<Subject subject_id="nonfiction_social_sciences_sociology_general">
Nonfiction -- Social Sciences -- Sociology -- General
</Subject>
<Subject
subject_id="professional_technical_engineering_general">Professional &
Technical -- Engineering -- General</Subject>
<Subject subject_id="science_general_aaaa0">Science --
General</Subject>
</Subjects>
</BookData>
</BookList>
</ISBNdb>
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
Loop
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
http://msdn.microsoft.com/en-us/library/h0hw012b.aspx
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!
Here's what I'm trying to do. I'm using http://isbndb.com 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 MSXML
Dim XMLURL
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 = "http://isbndb.com/api/books.xml?access_key=" + 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 =
MSXML.SelectSingleNode("ISBNdb/BookList/BookData/Subjects").Text
GetISBNSubject = SubjectNodes
Else
Debug.Print "The service is not available."
End If
End Function
Here's how a single record from isbn.net looks like:
<ISBNdb server_time="2008-10-27T15:27:15Z">
<BookList total_results="1" page_size="10" page_number="1"
shown_results="1">
<BookData
book_id="from_natural_complexity_to_a_world_knowledge_dialogue"
isbn="1420092286" isbn13="9781420092288">
<Title>
From Natural Complexity to a World Knowledge Dialogue
</Title>
<TitleLong>
From Natural Complexity to a World Knowledge Dialogue: Laying
Foundations for a World Knowledge Dialogue
</TitleLong>
<AuthorsText>
Frederic Darbellay (Editor), Moira Cockell (Editor), Jerome Billotte
(Editor), Francis Waldvogel (Editor)
</AuthorsText>
<PublisherText publisher_id="efpl_press">EFPL Press</PublisherText>
<Subjects>
<Subject subject_id="nonfiction_social_sciences_sociology_general">
Nonfiction -- Social Sciences -- Sociology -- General
</Subject>
<Subject
subject_id="professional_technical_engineering_general">Professional &
Technical -- Engineering -- General</Subject>
<Subject subject_id="science_general_aaaa0">Science --
General</Subject>
</Subjects>
</BookData>
</BookList>
</ISBNdb>
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
Loop
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
http://msdn.microsoft.com/en-us/library/h0hw012b.aspx
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!