Book list - getting book details from amazon using Excel VBA barcode lookups

vba excel amazon-web-services barcode
hawkeye picture hawkeye · Oct 11, 2010 · Viewed 16k times · Source

I have a barcode reader and bunch of books. For each of the books, I want to list the book name and the author in an Excel spreadsheet.

My view is that some VBA code connecting to an Amazon web service would make this easier.

My questions is - hasn't anyone done this before? Could you point me to the best example.

Answer

Dr. belisarius picture Dr. belisarius · Oct 15, 2010

I thought it was an easy one googling, but turned out more difficult than I expected.

In fact, I was unable to find a VBA ISBN based program to get book data from the web, so decided to do one.

Here is a VBA macro using the services from xisbn.worldcat.org. Examples here.. The services are free and don't need authentication.

To be able to run it you should check at Tools-> References (in the VBE window) the "Microsoft xml 6.0" library.

This macro takes the ISBN (10 digits) from the current cell and fills the following two columns with the author and title. You should be able to loop through a full column easily.

The code has been tested (well, a bit) but there is no error checking in there. 

 Sub xmlbook()
 Dim xmlDoc As DOMDocument60
 Dim xWords As IXMLDOMNode
 Dim xType As IXMLDOMNode
 Dim xword As IXMLDOMNodeList
 Dim xWordChild As IXMLDOMNode
 Dim oAttributes As IXMLDOMNamedNodeMap
 Dim oTitle As IXMLDOMNode
 Dim oAuthor As IXMLDOMNode
 Set xmlDoc = New DOMDocument60
 Set xWords = New DOMDocument60
 xmlDoc.async = False
 xmlDoc.validateOnParse = False
 r = CStr(ActiveCell.Value)

 xmlDoc.Load ("http://xisbn.worldcat.org/webservices/xid/isbn/" _
              + r + "?method=getMetadata&format=xml&fl=author,title")

 Set xWords = xmlDoc

     For Each xType In xWords.ChildNodes
         Set xword = xType.ChildNodes
         For Each xWordChild In xword
             Set oAttributes = xWordChild.Attributes
             On Error Resume Next
             Set oTitle = oAttributes.getNamedItem("title")
             Set oAuthor = oAttributes.getNamedItem("author")
             On Error GoTo 0
         Next xWordChild
     Next xType
  ActiveCell.Offset(0, 1).Value = oTitle.Text
  ActiveCell.Offset(0, 2).Value = oAuthor.Text
 End Sub

I did not go through Amazon because of their new "straightforward" authentication protocol ...

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More