html - 使用 Excel VBA 抓取 tr 类信息

Question

我在 Excel VBA 中创建了一个简单的程序，用于从https://www.nasdaq.com/market-activity/stocks/aapl/financials抓取表格中的所有信息。我在其他网站上使用过类似的代码，但由于某种原因，我无法让这个代码正常工作。谁能指出我所缺少的？任何帮助将非常感激。

“编辑代码”

Public Sub GetCompanyFinancials()

Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim HTMLTables As MSHTML.IHTMLElementCollection
Dim HTMLTable, HTMLRow, HTMLCell As MSHTML.IHTMLElement

XMLPage.Open "GET", "https://www.nasdaq.com/market-activity/stocks/aapl/financials", False
XMLPage.send

HTMLDoc.body.innerHTML = XMLPage.responseText

Set HTMLTables = HTMLDoc.getElementsByTagName("table")

For Each HTMLTable In HTMLTables
    For Each HTMLRow In HTMLTable.getElementsByTagName("tr")
        For Each HTMLCell In HTMLRow.Children
            Debug.Print HTMLCell.innerText
        Next HTMLCell
    Next HTMLRow
Next HTMLTable

End Sub

标签： htmlexcelvbaweb-scrapingfinance

Answer 1

我敢肯定有很多方法可以做这种事情。这是一个。

Sub Web_Table_Option_Two()
    Dim HTMLDoc As New HTMLDocument
    Dim objTable As Object
    Dim lRow As Long
    Dim lngTable As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Dim ActRw As Long
    Dim objIE As InternetExplorer
    Set objIE = New InternetExplorer
    objIE.Navigate "https://www.nasdaq.com/market-activity/stocks/aapl/financials"

    Do Until objIE.ReadyState = 4 And Not objIE.Busy
        DoEvents
    Loop
    Application.Wait (Now + TimeValue("0:00:03")) 'wait for java script to load
    HTMLDoc.body.innerHTML = objIE.Document.body.innerHTML
    With HTMLDoc.body
        Set objTable = .getElementsByTagName("table")
        For lngTable = 0 To objTable.Length - 1
            For lngRow = 0 To objTable(lngTable).Rows.Length - 1
                For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
                    ThisWorkbook.Sheets("Sheet1").Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
                Next lngCol
            Next lngRow
            ActRw = ActRw + objTable(lngTable).Rows.Length + 1
        Next lngTable
    End With
    objIE.Quit
End Sub

结果：