图书列表 - 使用 Excel VBA 条形码查找从亚马逊获取图书详细信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3903731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Book list - getting book details from amazon using Excel VBA barcode lookups
提问by hawkeye
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.
我有一个条形码阅读器和一堆书。对于每本书，我想在 Excel 电子表格中列出书名和作者。
My view is that some VBA code connecting to an Amazon web service would make this easier.
我的观点是一些连接到 Amazon Web 服务的 VBA 代码会使这变得更容易。
My questions is - hasn't anyone done this before? Could you point me to the best example.
我的问题是 - 以前没有人这样做过吗？你能给我指出最好的例子吗？
回答by Dr. belisarius
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.
事实上，我无法找到一个基于 VBA ISBN 的程序来从网上获取书籍数据，所以决定做一个。
Here is a VBA macro using the services from xisbn.worldcat.org. Examples here.. The services are free and don't need authentication.
这是一个使用xisbn.worldcat.org服务的 VBA 宏。例子在这里。. 这些服务是免费的，不需要身份验证。
To be able to run it you should check at Tools-> References (in the VBE window) the "Microsoft xml 6.0" library.
为了能够运行它，您应该检查工具-> 引用（在 VBE 窗口中）“Microsoft xml 6.0”库。
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.
此宏从当前单元格中获取 ISBN（10 位数字）并用作者和标题填充以下两列。您应该能够轻松地遍历整个列。
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 ...
我没有通过亚马逊，因为他们新的“直接”身份验证协议......
回答by Andrew Harris
This is has been enormously helpful for me!
这对我帮助很大！
I have updated the macro to allow it to cycle through a column of ISBN numbers until it reaches an empty cell.
我已经更新了宏以允许它循环遍历一列 ISBN 编号，直到它到达一个空单元格。
It also search for publisher, year and edition.
它还搜索出版商、年份和版本。
I have added some basic error checking if certain fields are not available.
如果某些字段不可用，我添加了一些基本的错误检查。
Sub ISBN()
Do
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,year,publisher,ed")
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")
Set oPublisher = oAttributes.getNamedItem("publisher")
Set oEd = oAttributes.getNamedItem("ed")
Set oYear = oAttributes.getNamedItem("year")
On Error GoTo 0
Next xWordChild
Next xType
On Error Resume Next
ActiveCell.Offset(0, 1).Value = oTitle.Text
On Error Resume Next
ActiveCell.Offset(0, 2).Value = oAuthor.Text
On Error Resume Next
ActiveCell.Offset(0, 3).Value = oPublisher.Text
On Error Resume Next
ActiveCell.Offset(0, 4).Value = oYear.Text
On Error Resume Next
ActiveCell.Offset(0, 5).Value = oEd.Text
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
End Sub
回答by Danny
I just found this thread as I was attempting to do the same thing. Unfortunately I'm on a MAC, so these answers don't help. With a bit of research I was able to do get it to work in MAC Excel with this module:
我刚刚在尝试做同样的事情时发现了这个线程。不幸的是，我使用的是 MAC，所以这些答案无济于事。通过一些研究，我能够使用这个模块让它在 MAC Excel 中工作：
Option Explicit
' execShell() function courtesy of Robert Knight via StackOverflow
' http://stackoverflow.com/questions/6136798/vba-shell-function-in-office- 2011-for-mac
Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long
Function execShell(command As String, Optional ByRef exitCode As Long) As String
Dim file As Long
file = popen(command, "r")
If file = 0 Then
Exit Function
End If
While feof(file) = 0
Dim chunk As String
Dim read As Long
chunk = Space(50)
read = fread(chunk, 1, Len(chunk) - 1, file)
If read > 0 Then
chunk = Left$(chunk, read)
execShell = execShell & chunk
End If
Wend
exitCode = pclose(file)
End Function
Function HTTPGet(sUrl As String) As String
Dim sCmd As String
Dim sResult As String
Dim lExitCode As Long
Dim sQuery As String
sQuery = "method=getMetadata&format=xml&fl=*"
sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
sResult = execShell(sCmd, lExitCode)
' ToDo check lExitCode
HTTPGet = sResult
End Function
Function getISBNData(isbn As String) As String
Dim sUrl As String
sUrl = "http://xisbn.worldcat.org/webservices/xid/isbn/" & isbn
getISBNData = HTTPGet(sUrl)
End Function
Function getAttributeForISBN(isbn As String, info As String) As String
Dim data As String
Dim start As Integer
Dim finish As Integer
data = getISBNData(isbn)
start = InStr(data, info) + Len(info) + 2
finish = InStr(start, data, """")
getAttributeForISBN = Mid(data, start, finish - start)
End Function
This is not all my original work, I pasted it together from another site, then did my own work. Now you can do things like:
这不是我所有的原创作品，我从另一个站点粘贴它，然后做我自己的工作。现在您可以执行以下操作：
getAttributeForISBN("1568812019","title")
getAttributeForISBN("1568812019","title")
This will return the title of that book. Of course you can apply this formula to all of the ISBNs in column A to look up multiple titles, or authors, or whatever.
这将返回那本书的标题。当然，您可以将此公式应用于 A 列中的所有 ISBN 以查找多个标题、作者或其他内容。
Hopefully this helps someone else out there!
希望这可以帮助其他人！
回答by Fionnuala
If the barcode is the ISBN, which seems likely, perhaps you can use: amazon.com/Advanced-Search-Books/b?ie=UTF8&node=241582011
如果条形码是 ISBN，这似乎很可能，也许您可以使用：amazon.com/Advanced-Search-Books/b?ie=UTF8&node=241582011
