VBA Access: XML, Error '91': Object variable or With block variable not set?

Question

Now that school is out for the summer and I have some free time I have chosen to create a personal book inventory system. While researching I came across this post by Jules: ISBN -> bookdata Lookup to fill in a database

When trying to implement my code below, I first got a Run-time error: Access is denied when using Set xmlhttp = CreateObject("MSXML2.xmlhttp"). I found a post (http://social.msdn.microsoft.com/Forums/en-US/1abda1ce-e23c-4d0e-bccd-a323aa7f2ea5/access-is-denied-while-using-microsoftxmlhttp-to-get-a-url-link-in-vbscript-help) that said to change the line to Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0").

I now am receiving Run-time error '91': Object variable or With block variable not set.

Anyone have any ideas on how to resolve this? I'm new to working with XML. For testing I am using the Immediate Window and typing testlookup("0007102968").

Module SearchISBN:

Option Compare Database
Option Explicit

Public Function testlookup(value As String)
    Dim book
    Set book = New isbn
    book.Lookup (value)
    Debug.Print book.Title
    Debug.Print book.PublisherText
End Function

Class Module isbn:

Option Compare Database
Option Explicit

'https://stackoverflow.com/questions/2454348/isbn-bookdata-lookup-to-fill-in-a-database
' AccessKeys created with account on ISBNDB.com
' Reference in (Tools->Refernces) made to "Microsoft XML"

Dim strTitle As String
Dim strAuthor As String
Dim strPublisher As String
Dim strSummary As String
Dim strPrice As Currency
Dim strISBN10 As Integer
Dim strISBN13 As Integer
Dim strNotes As String
'Dim strPersRating As String
Dim accessKey As String

Private Sub Class_Initialize()
    ' Set AccessKey value of ISBNDB API
    accessKey = "NSOY388Z"
End Sub
Property Get Title() As String
    Title = strTitle
End Property
Property Get Author() As String
    Author = strAuthor
End Property
Property Get Publisher() As String
    Publisher = strPublisher
End Property
Property Get Summary() As String
    Summary = strSummary
End Property
Property Get Price() As Currency
    Price = strPrice
End Property
Property Get ISBN10() As Integer
    ISBN10 = strISBN10
End Property
Property Get ISBN13() As Integer
    ISBN13 = strISBN13
End Property
Property Get Notes() As String
    Notes = strNotes
End Property

Public Function Lookup(isbn As String) As Boolean
    Lookup = False
'    Dim xmlhttp
'    Dim strTest As String
'    strTest = "https://isbndb.com/api/books.xml?access_key=" & accessKey & "&results=texts&index1=isbn&value1=" & isbn
'    Debug.Print strTest
'    'Run-time error, access is denied
'    ' Set xmlhttp = CreateObject("MSXML2.xmlhttp")
'    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
'    xmlhttp.Open "Get", strTest, False '"https://isbndb.com/api/books.xml?access_key=" & accessKey & "&results=texts&index1=isbn&value1=" & isbn, False
'    xmlhttp.send
'    Debug.Print xmlhttp.responseText
'    Debug.Print "Response: " & xmlhttp.responseXML.XML '
'    Dim xmldoc
''    Set xmldoc = CreateObject("Microsoft.XMLDOM")
''    xmldoc.loadXML (xmlhttp.responseXML.XML)
''    ERROR
''    If (xmldoc.selectSingleNode("//BookList").getAttribute("total_results") = 0) Then
''        MsgBox "Invalid ISBN or not in database"
''        Exit Function
''    End If
''    If (xmldoc.selectSingleNode("//BookList").getAttribute("total_results") > 1) Then
''        MsgBox "Caution, got more than one result!"
''        Exit Function
''    End If
'
'    Set xmldoc = New DOMDocument
'    xmldoc.loadXML (xmlhttp.responseText)
'
'
'
'    strTitle = xmldoc.selectSingleNode("//BookData/TitleLong").Text
'    strAuthor = xmldoc.selectSingleNode("//BookData/AuthorsText").Text
'    strPublisher = xmldoc.selectSingleNode("//BookData/PublisherText").Text
'    strNotes = xmldoc.selectSingleNode("//BookData/Notes").Text
'    strSummary = xmldoc.selectSingleNode("//BookData/Summary").Text
'

    Dim xmlhttp As MSXML2.xmlhttp
    Dim xmldoc As MSXML2.DOMDocument
    Dim XMLNodes As MSXML2.IXMLDOMNodeList
    Dim xmlElement As MSXML2.IXMLDOMElement
    Dim bookTitle As String
    Dim myErr As MSXML2.IXMLDOMParseError

    Dim strTest As String
    strTest = "https://isbndb.com/api/books.xml?access_key=" & accessKey & "&results=texts&index1=isbn&value1=" & isbn

    ' Fetch the XML - THIS IS WHERE I AM NOW GETTING ERROR
    xmlhttp.Open "Get", strTest, False '"https://isbndb.com/api/books.xml?access_key=" & accessKey & "&results=texts&index1=isbn&value1=" & isbn, False
    xmlhttp.send

    Set xmldoc = New DOMDocument
    xmldoc.loadXML (xmlhttp.responseText)

    Set XMLNodes = xmldoc.getElementsByTagName("BookData")

    Dim i As Integer

    ' Get the data
    For i = 1 To XMLNodes.length
        Set xmlElement = XMLNodes.nextNode
        bookTitle = xmlElement.getElementsByTagName("Title").Item(0).nodeTypedValue
    Next

    Lookup = True

End Function

EDIT: Still getting the error, but am now getting a response in the immediate window:

https://isbndb.com/api/books.xml?access_key=NSOY388Z&results=texts&index1=isbn&value1=0007102968
Response: 
<?xml version="1.0" encoding="UTF-8"?>

<ISBNdb server_time="2013-06-20T16:20:00Z">
<BookList total_results="1" page_size="10" page_number="1" shown_results="1">
<BookData book_id="the_times_book_of_quotations" isbn="0007102968" isbn13="9780007102969">
<Title>The Times book of quotations</Title>
<TitleLong></TitleLong>
<AuthorsText></AuthorsText>
<PublisherText publisher_id="times_books">[Glasgow] : Times Books : 2000.</PublisherText>
<Summary></Summary>
<Notes>Includes index.</Notes>
<UrlsText></UrlsText>
<AwardsText></AwardsText>
</BookData>
</BookList>
</ISBNdb>

Browser results of https request:

Jaycal · Answer 1 · 2013-06-25T03:59:40.687

1

Since XML in VBA can sometimes be a pain for me, I just take the XML response text and then load it into a MSXML2.DOMDocument. From there you can parse and get the data you need. First you need to make sure that the file has a reference to Microsoft XML dll (do this by selecting Tool > References from the Visual Basic window and then selecting "Microsoft XML 6.0").

As for the code, it would look something like this:

Dim xmlhttp As MSXML2.xmlhttp
Dim xmldoc As MSXML2.DOMDocument
Dim XMLNodes As MSXML2.IXMLDOMNodeList
Dim xmlElement As MSXML2.IXMLDOMElement
Dim bookTitle as String

' Fetch the XML
Set xmlhttp = CreateObject("Microsoft.xmlHTTP")
xmlhttp.Open "Get", yourURL, False
xmlhttp.send

' Create a new XMLDocument to which to load the XML text
Set xmlDoc = New DOMDocument
xmldoc.LoadXML (xmlhttp.responseText)

' Get all of the BookData Nodes and fetch the first node
Set XMLNodes = xmldoc.getElementsByTagName("BookData")

' Get your data (retrieved only the Title as an example)
for i = 1 to XMLNodes.length
    Set myElement = XMLNodes.NextNode
    bookTitle = myElement.getElementsByTagName("Title").Item(0).nodeTypedValue
Next

You can get rid of the for loop if you are sure that you'll only get one response (given that its isbn i guess you should... )

edited Jun 25 '13 at 03:59

answered Jun 20 '13 at 18:46

Jaycal

2,087
1
13
21

In my code, I have checks for 0 or >1 results. I tried yours, but I'm having issues in the `for` loop. Where/what as is `myChildren` defined? – Analytic Lunatic Jun 21 '13 at 13:47
Error on my part; updated the code to replace `myChildren` with `myElement`. Should work now – Jaycal Jun 21 '13 at 19:44
That helped some things, but now in my Class Module isbn I am getting the Error 91 on the following line: `xmlhttp.Open "Get", strTest, False`. Any ideas? – Analytic Lunatic Jun 21 '13 at 20:22
Update the line causing the error so that it is just `xmlhttp.Open "Get", strTest, False` – Jaycal Jun 21 '13 at 20:37
I'm not sure I'm understanding @Jaycal. Isn't what you have posted the exact same thing as how my current error line reads? – Analytic Lunatic Jun 24 '13 at 13:27
You updated your your Class Module ISBN with the code I provided, but the line right below where you say "THIS IS WHERE I'M GETTING THE ERROR" you print your URL at the end of that line. This is not needed because the string `strTest` has this URL already. Delete it so there's nothing after the word false and the line only reads `xmlhttp.Open "Get", strTest, False` – Jaycal Jun 24 '13 at 16:56
I know it's hard to see, but that bit of code you are talking about after the `False` is already commented out. It had nothing to do with the error. I got the code working after chewing it around a good bit. Thanks anyway. – Analytic Lunatic Jun 24 '13 at 19:10

VBA Access: XML, Error '91': Object variable or With block variable not set?

1 Answers1