6 Replies - 2433 Views - Last Post: 24 June 2013 - 09:18 AM Rate Topic: -----

#1 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

VBA Access: Error '91': Object variable or With block variable

Posted 21 June 2013 - 12:30 PM

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, decided to try using the ISBDN.com API. This way I could enter the ISBN of each book in my library and have the system automatically retrieve the information I wanted without needing to manual input such data myself.

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 (Link) 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. on the following line:

If (xmldoc.selectSingleNode("//BookList").getAttribute("total_results") = 0) Then


Anyone have any ideas on how to resolve this? I'm very 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

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

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 = "MYACCESSKEY"
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 "Response: " & xmlhttp.responseXML.XML '
    Debug.Print xmlhttp.responseText
    Dim xmldoc
    Set xmldoc = CreateObject("Microsoft.XMLDOM")
    xmldoc.loadXML (xmlhttp.responseXML.XML)
    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

    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

    Lookup = True

End Function



Results of 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:

Posted Image

Is This A Good Question/Topic? 0
  • +

Replies To: VBA Access: Error '91': Object variable or With block variable

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3224
  • View blog
  • Posts: 10,825
  • Joined: 12-December 12

Re: VBA Access: Error '91': Object variable or With block variable

Posted 21 June 2013 - 01:28 PM

If (xmldoc.selectSingleNode("//BookList").getAttribute("total_results") = 0) Then

You should split this line into two, to assist with your debugging.

This page discusses the error message you have, in relation to selectSingleNode. It may be that selectSingleNode doesn't work, in which case its return value is null, and attempting to use getAttribute on null generates the error. Use Is Nothing to resolve this.

I am not sure why it is not working. (Is it necessary to use responseXML.XML ?)

BTW Why not check for "1" initially, to agree with the data that you have retrieved? You can switch to "0" later.

Maybe BookList is root, and not considered a node(?)

You might also try a different XPath method than selectSingleNode.
Was This Post Helpful? 0
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: VBA Access: Error '91': Object variable or With block variable

Posted 21 June 2013 - 08:55 PM

Maybe the the xmlDoc variable has never been set? I don't see anything about it anywhere else.
Was This Post Helpful? 0
  • +
  • -

#4 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Access: Error '91': Object variable or With block variable

Posted 24 June 2013 - 06:42 AM

View PostBobRodes, on 22 June 2013 - 03:55 AM, said:

Maybe the the xmlDoc variable has never been set? I don't see anything about it anywhere else.


I can't say I understand what you are asking about Bob?

I've modified my code as shown below, but am now receiving the error, Run-time error '91': Object variable or With block variable not set on line:

xmlhttp.Open "Get", strTest, False 





SearchISBN Module:

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


isbn Class Module:

Option Compare Database
Option Explicit

' 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
    xmlhttp.Open "Get", strTest, 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



Document Tree:

http://isbndb.com/ap...lue1=0007102968
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3224
  • View blog
  • Posts: 10,825
  • Joined: 12-December 12

Re: VBA Access: Error '91': Object variable or With block variable

Posted 24 June 2013 - 07:46 AM

Quote

Object variable or With block variable not set

This is one of the most common errors. You have an object variable xmlhttp which is capable of referring to an object, but it doesn't yet refer to an object-instance. Try

Dim xmlhttp As New MSXML2.xmlhttp

This post has been edited by andrewsw: 24 June 2013 - 07:46 AM

Was This Post Helpful? 0
  • +
  • -

#6 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Access: Error '91': Object variable or With block variable

Posted 24 June 2013 - 07:56 AM

View Postandrewsw, on 24 June 2013 - 02:46 PM, said:

Quote

Object variable or With block variable not set

This is one of the most common errors. You have an object variable xmlhttp which is capable of referring to an object, but it doesn't yet refer to an object-instance. Try

Dim xmlhttp As New MSXML2.xmlhttp


I changed the line as suggested and was able to get to the next line, xmlhttp.send, where I received:

Run-time error '-2147024891 (80070005)': Access is denied

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3224
  • View blog
  • Posts: 10,825
  • Joined: 12-December 12

Re: VBA Access: Error '91': Object variable or With block variable

Posted 24 June 2013 - 09:18 AM

Print out the value of strTest and enter it into the browser's address bar to see if it generates results.

The querystring part of this link should be url-encoded, probably using UrlEncode. If the querystring arguments are entirely alpha-numeric then this is not the issue, but the querystring should be encoded anyway.

There remains the possibility that "Access is denied", that you cannot get the content remotely.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1