亚马逊销售数据（含Excel VBA） [英] Amazon Sales Data (with Excel VBA)
问题描述
我正在尝试通过Excel VBA获取我搜索的每个关键字的结果编号（在HTML代码中）。通过className，id和data-asin缩小标准，但是由于VBA不支持该参考库，所以最后一个证明是棘手的。
I'm trying to obtain the result number (in the HTML code) of each keyword I search by means of Excel VBA. Narrowing down the criteria by className, id, and data-asin, but that last one is proving to be tricky since VBA doesn't support that reference library yet.
此代码的结构要做：
-
amazon.com并进入搜索栏。
Go onto amazon.com and go to the search bar.
循环以列C开头。从SearchTerm1列中搜索一个搜索字词并进行搜索。
The loop starts with column C. Pull a search term from the SearchTerm1 column and search.
加载结果页后，请尝试通过className，ID（均在HTML代码中找到）和ASIN号码（该数字从列中拉出）查找指定的产品B，以匹配搜索结果页面上的数据的asin值）。没有所有3个标准，如果在第一个结果页面上列出，excel将无法找到该产品。
Once the results page is loaded, try and find the specified product by className, ID (both found in the HTML code), and ASIN number (this number is pulled from column B in order to match the data-asin value on the search results page). Without all 3 criteria, excel won't be able to find the product if it's listed on the first results page.
下面的截图是仅基于className和ID标准的代码，它从页面中提取最后一个产品结果，这不是我的产品分析方法。
The screenshot below is the code only pulling based on the className and ID criteria, and it pulls the last product result from the page, which is not what my goal for analysation of how products are doing.
仅当产品是第一个结果在搜索页面，这意味着某些东西终于可以工作，但是缺少一两个步骤来从页面中获取所有的产品位置。
The code included only pulls the product rank if the product is the first result on the search page, which means something is finally working, but is missing a step or two to grab all of the product positions from the page.
任何帮助或推动正确的方向将是高度的我希望VBA在这些销售研究方面更加多才多艺，到目前为止，这是奇迹，但是我可能达到极限，代码如下。
Any help or push in the right direction would be highly appreciated. I wish VBA were more versatile for these kinds of sales research things. It's done wonders so far, but I may be reaching its limit. Code is below.
Sub AmazonSearchRank()
Dim MyHTML_Element As IHTMLElement
Dim MyURL As String
Dim AASearchRank As Workbook
Dim AAws As Worksheet
Dim InputSearchOrder As HTMLInputElement
Dim elems As IHTMLElementCollection
Dim TDelement As HTMLTableCell
Dim InputSearchButton As HTMLInputButtonElement
Dim IE As InternetExplorer
Dim AASearchTerms As Workbook
Dim SearchTermsSheet As Worksheet
Dim x As Integer
Dim i As Long
MyURL = "https://www.amazon.com"
Set IE = New InternetExplorer
With IE
.Silent = True
.Navigate MyURL
.Visible = True
Do
DoEvents
Loop Until .ReadyState = READYSTATE_COMPLETE
End With
Set HTMLDoc = IE.Document
Set AASearchRank = Application.Workbooks.Open("C:\Users\CompanyName\Desktop\Automation Anywhere\Sample_Items_For_SearchRank.xls")
Set AAws = AASearchRank.Worksheets("Sheet1")
Set InputSearchButton = HTMLDoc.getElementById("nav-search-submit-text")
Set InputSearchOrder = HTMLDoc.getElementById("twotabsearchbox")
If Not InputSearchOrder Is Nothing Then
InputSearchButton.Click
Do
DoEvents
Loop Until IE.ReadyState = READYSTATE_COMPLETE
End If
x = 2
If AAws.Range("D" & x).Value = "" Then
Do Until AAws.Range("B" & x) = ""
Set InputSearchOrder = HTMLDoc.getElementById("twotabsearchtextbox")
InputSearchOrder.Value = AAws.Range("C" & x)
Set InputSearchButton = HTMLDoc.getElementsByClassName("nav-input")(0)
InputSearchButton.Click
Do
DoEvents
Loop Until IE.ReadyState = READYSTATE_COMPLETE
Application.Wait (Now + TimeValue("0:00:05"))
Set elems = HTMLDoc.getElementsByClassName("s-result-item celwidget")
i = 2
For Each TDelement In elems
If TDelement.className = "s-result-item celwidget" And InStr(TDelement.ID, "result") InStr(TDelement.innerHTML, AAws.Range("B" & x).Value) Then
AAws.Range("D" & x).Value = TDelement.ID
i = i + 1
End If
Next
x = x + 1
Loop
End If
End Sub
推荐答案
以下是从表单中列出的每个搜索查询下载Amazon的产品的示例条款，并使用ASIN和说明填充表格产品。它使用XHR，所以不需要IE。代码如下：
Here is the example which downloads products from Amazon for each search query presented on the sheet Terms, and populates the sheet Products with ASINs and descriptions. It uses XHR, so IE isn't needed. The code is as follows:
Sub Test()
lngRow = 1
' search each term
For Each strTerm In Sheets("Terms").UsedRange
lngPage = 1
Do
' HTTP GET request of the search result page
strUrl = "https://www.amazon.com/s/ref=nb_sb_noss_2?page=" & lngPage & "&keywords=" & EncodeUriComponent(strTerm)
Set objXHR = CreateObject("MSXML2.XMLHttp")
objXHR.Open "GET", strUrl, False
objXHR.Send
strResp = objXHR.ResponseText
' split response to array by items
arrResp = Split(strResp, "<li id=""result_")
' process each item on the page
For i = 1 To UBound(arrResp)
strItem = arrResp(i)
' extract ASIN
strTmp = Split(strItem, "data-asin=""")(1)
strTmp = Split(strTmp, """")(0)
Sheets("Products").Cells(lngRow, 1).NumberFormat = "@"
Sheets("Products").Cells(lngRow, 1).Value = strTmp
' extract the product description
strTmp = Split("<li id=""result_" & strItem, "</li>")(0) & "</li>"
Sheets("Products").Cells(lngRow, 2).Value = GetInnerText(strTmp)
' show current item
Sheets("Products").Cells(lngRow, 1).Select
' next row
lngRow = lngRow + 1
Next
' adjust sheet
Sheets("Products").Columns.AutoFit
Sheets("Products").Rows.AutoFit
' next page
lngPage = lngPage + 1
Loop Until UBound(arrResp) = 0 ' empty search result
Next
End Sub
Function EncodeUriComponent(strText)
Static objHtmlfile As Object
If objHtmlfile Is Nothing Then
Set objHtmlfile = CreateObject("htmlfile")
objHtmlfile.parentWindow.execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
End If
EncodeUriComponent = objHtmlfile.parentWindow.encode(strText)
End Function
Function GetInnerText(strHtmlContent)
Dim objHtmlFile, objBody
Set objHtmlFile = CreateObject("htmlfile")
objHtmlFile.write strHtmlContent
Set objBody = objHtmlFile.getElementsByTagName("body")(0)
GetInnerText = Trim(objBody.innerText)
End Function
放在条款表单上：
产品表中的结果包含571项：
Results on the Product sheet contain 571 items:
这不是一个完整的答案，但希望能帮助你。
It's not a complete answer, but I hope it helps you.
这篇关于亚马逊销售数据（含Excel VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
