时间戳

首页 > 解决方案 > 使用 Classname 从网站将数据复制到使用 VBA 的 Excel

excel - 使用 Classname 从网站将数据复制到使用 VBA 的 Excel

问题描述

我正在尝试从网站获取信息，但无法获取任何数据。 https://markets.cboe.com/us/equities/market_statistics/book/AAPL/ 我在这篇文章中遇到了同样的问题R rvest retrieve empty table 如何在 VBA 中做到这一点？我在互联网上找到了这段代码，我对其进行了修改，但它没有获取数据，并且信息显示在一个窗口中，而不是粘贴到电子表格中。如果您直接导出整个表格，那就太好了，否则我将不得不逐个单元格地复制。提前致谢！

Sub Get_Web_Data()
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant
' Website to go to.
website = "https://markets.cboe.com/us/equities/market_statistics/book/AAPL/"
' Create the object that will make the webpage request.
Set request = CreateObject("MSXML2.XMLHTTP")
' Where to go and how to go there - probably don't need to change this.
request.Open "GET", website, False
' Get fresh data.
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
' Send the request for the webpage.
request.send
' Get the webpage response data into a variable.
response = StrConv(request.responseBody, vbUnicode)
' Put the webpage into an html object to make data references easier.
html.body.innerHTML = response
' Get the price from the specified element on the page.
price = html.getElementsByClassName("book-viewer__ask book-viewer__ask-shares").Item(2).innerText
' Output the price into a message box.
MsgBox price
End Sub

标签： excelvbaweb-scraping

解决方案

而是使用 Internet Explorer 并在 vba 中打开 Microsoft Internet 控件，我使用此代码并设法从同一网站提取数据并粘贴到单元格中；

子互联网（）

Dim IEe As InternetExplorer
    Dim doc, element
    Set IEe = New InternetExplorer

    IEe.Visible = TRUE
    IEe.Navigate "https://markets.cboe.com/us/equities/market_statistics/book/AAPL/"

       Do While IEe.ReadyState = 4: DoEvents: Loop
    Do Until IEe.ReadyState = 4: DoEvents: Loop

Set element = IEe.Document.getElementByID("id="ext-gen1057"") 'to get the ID, right click in internet explorer and pick the inspect element option the click on the data you want 
thisworkbook.sheets("sheet1").range("a1")= element.value  

end sub

推荐阅读