使用excel vba从网站获取特定值-Java 学习之路

我正在尝试使用Excel VBA从网站获取特定文本 . 但我不知道该怎么做 . 请帮忙！

我想获得突出显示的html元素的值“52.05”：

enter image description here

这是我的代码：

rowNum = 10
While rowNum < 51
    Do
        DoEvents
        Loop Until ie.readyState = READYSTATE_COMPLETE

    Set doc = ie.document
    On Error Resume Next

    output = doc.getElementById("yfi_quote_summary_data")
    Sheet1.Cells(rowNum, "E").Value = output

    rowNum = rowNum + 1
    ticker = Sheet1.Range("B" & rowNum).Value
    ie.navigate "http://finance.yahoo.com/q?s=" & ticker
Wend
ie.Quit

1 回答

试试这个 .

Sub Get_Values()
Dim RowNum As Long
Dim ie As InternetExplorer
Dim ticker As String
Dim MainDocument As IHTMLDocument
Dim TableHTML, PrevClose As Object
Set ie = New InternetExplorer

ie.Visible = True

RowNum = 10

    ticker = Sheets("Sheet1").Range("B" & RowNum).Value
    ie.navigate "http://finance.yahoo.com/q?s=" & ticker

While RowNum < 51
    'LOOP UNTIL EMPTY COLUMN "B"
    Do Until Sheets("Sheet1").Range("B" & RowNum).Value = ""

    'WAIT FOR PAGE LOAD
    Do While Not ie.readyState = READYSTATE_COMPLETE
        DoEvents
        Application.Wait (Now + TimeValue("0:00:01"))
    Loop

    'SET PAGE VARIABLES
    Set MainDocument = ie.document
    Set TableHTML = MainDocument.getElementById("yfi_quote_summary_data")

    'FIND "PREV CLOSE" SECTION OF TABLE
    For Each tableheader In TableHTML.getElementsByTagName("th")
        If tableheader.innerHTML = "Prev Close:" Then
        Set PrevClose = TableHTML.getElementsByTagName("td")(0)
        Sheet1.Cells(RowNum, "E").Value = PrevClose.innerHTML
        Exit For
        End If
    Next tableheader

    'CONTINUE TO NEXT ROW
    RowNum = RowNum + 1
    ticker = Sheet1.Range("B" & RowNum).Value
    ie.navigate "http://finance.yahoo.com/q?s=" & ticker

    Loop
Wend

ie.Quit

End Sub

要获取 Bid 值，只需在代码中更改此行即可 .

If tableheader.innerHTML = "Prev Close:" Then

将此更改为

If tableheader.innerHTML = "Bid:" Then

回复于 2024-05-05T20:49:44+08:00

使用excel vba从网站获取特定值

1 回答

相关问题