vba - 使用 Excel vba 抓取网站

Question

所以我是 vba 的新手，我正在尝试获取价格(我尽我所知)。宏是:

Sub Deneme()

Dim objIE As InternetExplorer
Dim Prc1 As String
Set objIE = New InternetExplorer
Dim Search_Terms() As Variant
Dim CopiedData() As Variant


Dim y As Integer
objIE.Visible = False


Search_Terms = Application.Transpose(ActiveSheet.Range("A2:A169").Value)

ReDim CopiedData(LBound(Search_Terms) To UBound(Search_Terms))


y = 2
For a = LBound(Search_Terms) To UBound(Search_Terms)


objIE.navigate "https://steamcommunity.com/market/listings/578080/" & Search_Terms(a)
Do: DoEvents: Loop Until objIE.readyState = 4
Prc1 = objIE.document.getElementsByClassName("market_commodity_orders_table")(4).getElementsByTagName("tr")(1).textContent '<----- the problem is here
ActiveSheet.Range("D" & y).Value = Prc1

y = y + 1
Next


objIE.Quit

End Sub

网址是 THIS我试图得到这个值:

大多数错误是:

运行时错误“91”:
未设置对象变量或 With block 变量。

调试是:

objIE.document.getElementsByClassName("market_commodity_orders_table")(4).getElementsByTagName("tr")(1).textContent

Answer 1

在我为您测试我的新代码的过程中，我意识到除了您尝试使用的类名不存在之外，您还有其他问题。

另一个问题是文档在其他一些资源之前加载 - 这可能是由于该站点每秒更新价格(因此价格最初并未加载到 objIE.Document 对象中)。

为了解决这个问题，我添加了几个循环来等待您的对象可用。这应该适合你。

Sub Deneme()

    Dim objIE As InternetExplorer
    Dim Prc1 As String
    Set objIE = New InternetExplorer
    Dim Search_Terms() As Variant
    Dim CopiedData() As Variant
    Dim y As Integer
    Dim elemObj As Object

    objIE.Visible = False

    Search_Terms = Application.Transpose(ActiveSheet.Range("A2:A169").Value)

    ReDim CopiedData(LBound(Search_Terms) To UBound(Search_Terms))

    y = 2
    For a = LBound(Search_Terms) To UBound(Search_Terms)

        objIE.navigate "https://steamcommunity.com/market/listings/578080/" & Search_Terms(a)
        Do: DoEvents: Loop Until objIE.readyState = 4

        Do While Prc1 = ""
            Do While elemObj Is Nothing
                Set elemObj = objIE.document.getElementById("market_commodity_buyrequests")
                Set elemObj = elemObj.getElementsByClassName("market_commodity_orders_header_promote")(1)
            Loop
            Prc1 = elemObj.innerText
        Loop

        ActiveSheet.Range("D" & y).Value = Prc1
        Set elemObj = Nothing
        Prc1 = vbNullString

        y = y + 1

    Next

    objIE.Quit

End Sub