Excel VBA 抓取亚马逊库存

Question

我正在寻找抓取亚马逊库存..这是我使用的链接 https://www.amazon.com/Stratford-Pharmaceuticals-Omega-Fatty-Strength/dp/B006JCU54Y/ref=sr_1_2?s=pet-supplies&ie=UTF8&qid=1518816130&sr=1-2&keywords=stratford

有一个标题为“与类似商品比较”的部分，我需要提取价格(我已经这样做了)以及库存数量..第二部分不是直接获得的..我必须手动单击“添加到购物车”，然后从下一页单击“购物车”，然后从下一页选择“数量下拉列表并选择 10+ 并手动输入任何大数字，例如 999然后点击“更新”将会出现包含剩余库存的警报消息，如下所示(该卖家只有 35 个可用。要查看其他卖家是否有更多可用，)>> 所以这是所需的数量，即 35这是说明手动步骤的 Excel 文件和快照..我使用 IE，但如果可以使用 XMLHTTP 那当然很棒

这是我到目前为止设计的代码

Sub Test()
    Dim ws As Worksheet
    Dim ie          As Object
    Dim allLnks     As Object
    Dim lnk         As Object
    Dim r           As Long
    Dim liElem As Object
    Dim prElem As Object
    Dim crtElem As Object
    Dim elem As Object
    Dim cnt As Integer
    Dim inputElem As Object
    Dim inputEle As Object
    
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    Set ie = CreateObject("InternetExplorer.Application")

    With ie
        .Visible = True
        .navigate ("https://www.amazon.com/Stratford-Pharmaceuticals-Omega-Fatty-Strength/dp/B006JCU54Y/ref=sr_1_2?s=pet-supplies&ie=UTF8&qid=1518816130&sr=1-2&keywords=stratford")

        Do: DoEvents: Loop Until .readystate = 4
        
        ws.Range("B2").Value = Format(Now(), "dd/mm/yyyy - hh:mm:ss")
        Set liElem = .document.getelementbyid("detail-bullets").getelementsbytagname("table")(0).getelementsbytagname("ul")(0)
        
        For Each elem In liElem.getelementsbytagname("li")
            If InStr(elem.innerText, "ASIN") > 0 Then ws.Range("B1").Value = Replace(elem.innerText, "ASIN: ", "")
            If InStr(elem.innerText, "Rank:") > 0 Then ws.Range("B3").Value = MyUDF(elem.innerText, "Rank: ", "(")
            If InStr(elem.innerText, "Review:") > 0 Then ws.Range("B4").Value = Replace(Split(Trim(Split(elem.innerText, "Review: ")(1)), vbLf)(1), Chr(13), "")
        Next elem
        
        Set prElem = .document.getelementbyid("comparison_price_row")
        For Each elem In prElem.getelementsbytagname("td")
            cnt = cnt + 1
            ws.Range("A" & cnt + 4).Value = "Seller " & cnt
            ws.Range("B" & cnt + 4).Value = elem.getElementsByClassName("a-offscreen")(0).innerText
        Next elem
        
        cnt = 0
        Set crtElem = .document.getelementbyid("HLCXComparisonTable").getElementsByClassName("a-button-inner")
        For Each elem In crtElem
            .navigate elem.getelementsbytagname("a")(0).href
            Do: DoEvents: Loop Until .readystate = 4
            .navigate .document.getElementsByClassName("a-button-inner")(0).getelementsbytagname("a")(0).href
            Do: DoEvents: Loop Until .readystate = 4
            
            cnt = cnt + 1
            ws.Range("C" & cnt + 4).Value = Replace(Split(Split(MyUDF(.document.getElementsByClassName("a-row a-spacing-base sc-action-quantity sc-action-quantity-right")(0).innerHTML, "maxlength=", "quantity="), "autocomplete")(0), "=")(1), """", "")
        Next elem
        
        Stop
        '.Quit
    End With
End Sub

Function MyUDF(s As String, b As String, a As String) As String
    Dim arr()       As String
    Dim r           As String

    arr = Split(s, b)

    If UBound(arr) > 0 Then
        r = arr(1)
        arr = Split(r, a)

        If UBound(arr) > 0 Then
            r = arr(0)
        End If
    End If

    MyUDF = Trim(r)
End Function

以下是可能有帮助的快照

] 4

Answer 1

用于获取股票信息的 CSS 选择器

从您的代码中获取以下示例:

您可以使用 CSS 选择器来定位有关库存水平的文本。

.sc-product-availability

<小时/>

使用购物车 View 页面的 CSS 查询示例(由您的代码生成):

例如CSS查询关联cart view html

. 是 ClassName 的选择器。

<小时/>

VBA

您可以使用.document.querySelectorAll方法检索匹配项的nodeList(示例中为2)

Dim nodeList As Object
Set nodeList = .document.querySelectorAll(".sc-product-availability")

然后，您将循环其长度以检索项目(未经测试，但这是通用方法)。

Dim i As Long
For i = 0 to nodeList.Length - 1
    Debug.Print nodeList.Item(i).innerText
Next i

希望这对您有用。