书籍列表 - 使用Excel VBA条形码查找从亚马逊获取书籍详细信息 [英] Book list - getting book details from amazon using Excel VBA barcode lookups

问题描述

我有一个条形码阅读器和一堆书。对于每本书，我想在Excel电子表格中列出书名和作者。

我的观点是，连接到亚马逊网络服务的一些VBA代码会使这更容易。

我的问题是 - 以前没有人做过吗？你能指点我最好的例子吗

解决方案

我以为这是一个容易的谷歌搜索，但变得比我预期的更困难。

其实我没有找到一个基于VBA ISBN的程序来从网上获取图书数据，所以决定做一个。

以下是使用 xisbn.worldcat.org的服务的VBA宏。示例 here。。这些服务是免费的，不需要身份验证。

为了能够运行它，您应该检查工具 - >参考（在VBE窗口中）Microsoft xml 6.0库。

此宏从当前单元格中获取ISBN（10位数），并填写以下作者和标题的两列。您应该可以轻松地循环访问一个完整的列。

代码已经过测试（好吧，有一点），但是没有错误检查。

  Sub xmlbook（）
 Dim xmlDoc As DOMDocument60 
 Dim xWords As IXMLDOMNode 
 Dim xType作为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 
设置xWords =新建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）
 
设置xWords = xmlDoc 
 
对于xWords.ChildNodes中的每个xType 
设置xword = xType.ChildNodes 
对于每个xWordChild在xword中
设置oAttributes = xWordChild.Attributes 
 On Error Resume Next 
设置oTitle = oAttributes.getNamedItem（title）
设置oAuthor = oAttributes.getNamedItem（author）
错误GoTo 0 
下一个xWordChild 
下一个xType 
 ActiveCell.Offset（0，1）.Value = oTitle.Text 
 ActiveCell.Offset（0，2）.Value = oAuthor.Text 
 End Sub 
  

我没有通过亚马逊，因为他们新的直接身份验证协议...

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.

解决方案

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 ...

这篇关于书籍列表 - 使用Excel VBA条形码查找从亚马逊获取书籍详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！