html - vba 从 web html 元素导入(价格)

Question

我是 VBA 新手，我想使用 VBA 从网站将以太坊价格导入 Excel。

Sub go()
Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")

With appIE
    .navigate "https://www.coingecko.com/de/munze/ethereum.html"
    .Visible = True
End With

Do While appIE.Busy
    DoEvents
Loop

Set getPrice = appIE.document.getElementsByTagName("span.currency-exchangable")
Dim myValue As String: myValue = getPrice.innerText

appIE.Quit
Set appIE = Nothing

Range("B1").Value = myValue

End Sub

我还在引用下激活了 Microsoft Internet Control , Microsoft Object HTML Library和 Microsoft ActiveX Data Objects 6.1 library .

错误信息是

Run time error 438 - Object doesn't support this property or method.

我只想导入以太坊的价格 = $1,371.43

Answer 1

您可以使用以下内容。

笔记:

我在最后提供的链接优先选择 XMLHTTP60通过 Internet Explorer 检索 HTML。根据我的经验，当然更快。

另外，不要使用保留字 Go为过程名称。它会抛出一个错误。

您需要按类名访问。

Sub Testing()
'ms html library reference
Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")

With appIE
    .navigate "https://www.coingecko.com/de/munze/ethereum.html"
    .Visible = True
End With

Do While appIE.Busy
    DoEvents
Loop

Dim HTMLDoc As MSHTML.HTMLDocument
Set HTMLDoc = appIE.Document

Dim HTMLItemCol As MSHTML.IHTMLElementCollection

Set HTMLItemCol = HTMLDoc.getElementsByClassName("currency-exchangable")

Dim myValue As String
myValue = HTMLItemCol.Item(1).innerText

appIE.Quit
Set appIE = Nothing

ActiveSheet.Range("B1").Value = myValue

End Sub

引用: Excel VBA Introduction Part 47 - Browsing to Websites and Scraping a Web Page

通过在此处添加对 Microsoft XML 库的引用，可以更快地检索信息:

Option Explicit

Sub Testing()

'MS XML

Dim xmlpage As New XMLHTTP60
Dim HTMLDoc As New HTMLDocument

xmlpage.Open "GET", "https://www.coingecko.com/de/munze/ethereum.html", False 'requires well formed URL
xmlpage.send

HTMLDoc.body.innerHTML = xmlpage.responseText

Dim HTMLItemCol As MSHTML.IHTMLElementCollection

Set HTMLItemCol = HTMLDoc.getElementsByClassName("currency-exchangable")

Dim myValue As String
myValue = HTMLItemCol.Item(1).innerText

ActiveSheet.Range("B1").Value = myValue

End Sub