时间戳

首页 > 解决方案 > 怎么抢

excel - 怎么抢

问题描述

我一直在为一个项目学习 VBA，我无法从 html 中抓取某些元素并将它们填充到 excel 电子表格中。

我使用的代码没有返回任何错误，据我所知它应该可以工作。

这是我的 VBA 代码：

Option Explicit

Public Sub GrabShipping()

    Dim t As Date

    Dim ele As Object

    Dim driver As New ChromeDriver

    Dim post As WebElement

    Dim i As Integer

    Dim mysheet As Worksheet


    Const MAX_WAIT_SEC As Long = 10
    Const INURL = "https://ss3.shipstation.com/#/dashboard"
    Const URL = "https://ss3.shipstation.com/"

    Set mysheet = Sheets("Main")

    With driver
        .Start "Chrome"
        .get URL
        t = Timer
        Do
            On Error Resume Next
            Set ele = .FindElementById("username")
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
        Loop While ele Is Nothing
        If ele Is Nothing Then Exit Sub
        ele.SendKeys "Username"
        .FindElementById("password").SendKeys "Password"
        .FindElementById("btn-login").Click
     End With

     With driver
        .get INURL

        i = 2
        For Each post In driver.FindElementsByXPath("//div[contains(@class,'row-fluid stats')]")
            mysheet.Cells(i, 1) = post.FindElementByXPath(".//*[following-sibling:[contains(text(),'New Orders'").Attribute("New Orders")
            mysheet.Cells(i, 2) = post.FindElementByXPath(".//*[following-sibling:[contains(text(),'Ready to Ship'").Attribute("Ready to Ship")
            mysheet.Cells(i, 3) = post.FindElementByXPath(".//*[following-sibling:[contains(text(),'Orders Shipped'").Attribute("Orders Shipped")
        Next post


        Stop               '<==delete me later
        .Quit


    End With


End Sub

这是我试图从中获取的 HTML：

<div class="header row-fluid"><div class="row-fluid stats">
    <div class="col-sm-4 col-md-4 col-lg-4">
        <h2>2,318</h2>
        New Orders
    </div>
    <div class="col-sm-4 col-md-4 col-lg-4">
        <h2>53</h2>
        Ready to Ship
    </div>
    <div class="col-sm-4 col-md-4 col-lg-4">
        <h2>2,265</h2>
        Orders Shipped
    </div>
</div></div>

我希望它将 s 中的值返回到我的电子表格，但目前当我运行代码时，它不会导致任何内容被添加。

标签： excelvbaseleniumweb-scrapingselenium-chromedriver

解决方案

您可以使用 css 选择器组合

Dim item As Object, nodeList As Object, r As Long
Set nodeList = driver.findElementsByCss(".col-sm-4.col-md-4.col-lg-4 h2")
For each item in nodeList
    r = r + 1
    Activesheet.Cells(r,1) = item.text
Next

您可以尝试重新使用定时循环

 Dim item As Object, nodeList As Object, r As Long
 t = Timer
 Do
     Set nodeList = driver.FindElementsByCss(".col-sm-4.col-md-4.col-lg-4 h2")
     If Timer - t > MAX_WAIT_SEC Then Exit Do
 Loop While nodeList.Count = 0
 If nodeList.Count > 0 Then
     For Each item In nodeList
         r = r + 1
         ActiveSheet.Cells(r, 1) = item.Text
     Next
 End If

我建议您查看是否可以缩短 css 选择器，例如：

.col-sm-4 h2

推荐阅读