使用 Excel VBA 从网站（HTML div 类）中提取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41558714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pull value from website (HTML div class) using Excel VBA
提问by emiwark
I'm trying to automate manually going to this website and pulling the ratings from several apps every month for tracking purposes.
我正在尝试自动手动访问该网站并每月从多个应用程序中提取评级以进行跟踪。
I've figured out how to navigate and login to the page I want, but I'm having trouble pulling the actual element - the number "3.3" in this case - from this specific section into excel.
我已经想出了如何导航和登录到我想要的页面，但是我无法将实际元素（在本例中为数字“3.3”）从该特定部分提取到 excel 中。
Being unfamiliar with HTML in VBA, I've only gotten this far following tutorials/other questions but haven't yet been able to find something that works.
由于不熟悉 VBA 中的 HTML，我只在教程/其他问题之后得到了这么远，但还没有找到有效的东西。
Rating on website and the code behind it
Sub PullRating()
Dim HTMLDoc As HTMLDocument
Dim ie As InternetExplorer
Dim oHTML_Element As IHTMLElement
Dim sURL As String
On Error GoTo Err_Clear
sURL = "https://www.appannie.com/account/login/xxxxxxxxxx"
Set ie = New InternetExplorer
ie.Silent = True
ie.navigate sURL
ie.Visible = True
Do
'Wait until the Browser is loaded
Loop Until ie.readyState = READYSTATE_COMPLETE
Set HTMLDoc = ie.Document
HTMLDoc.all.Email.Value = "[email protected]"
HTMLDoc.all.Password.Value = "xxxxx"
For Each oHTML_Element In HTMLDoc.getElementById("login-form")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
Dim rating As Variant
Set rating = HTMLDoc.getElementsByClassName("rating-number ng-binding")
Range("A1").Value = rating
'ie.Refresh 'Refresh if required
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub
回答by Ryszard J?draszyk
The code below will let you extract text from first element with class name "rating-number ng-binding" in HTML document. By the way GetElementsByClassName is supported since IE 9.0. I use coding compatible also with older versions in my example.
下面的代码将让您从 HTML 文档中类名为“rating-number ng-binding”的第一个元素中提取文本。顺便说一下，从 IE 9.0 开始支持 GetElementsByClassName。在我的示例中，我使用的编码也与旧版本兼容。
Dim htmlEle1 as IHTMLElement
For Each htmlEle1 in HTMLDoc.getElementsByTagName("div")
If htmlEle1.className = "rating-number ng-binding" then
Range("A1").Value = htmlEle1.InnerText
Exit For
End if
Next htmlEle1
回答by ClintB
While Ryszards code should do the trick if you want to use the code you have already written then here is the alterations I believe you need to make.
如果您想使用已经编写的代码，Ryszards 代码应该可以解决问题，但这里是我认为您需要进行的更改。
For Each oHTML_Element In HTMLDoc.getElementById("login-form")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
'Need to wait for page to load before collecting the value
Loop Until ie.readyState = READYSTATE_COMPLETE
Dim rating As IHTMLElement
Set rating = HTMLDoc.getElementsByClassName("rating-number ng-binding")
'Need to get the innerhtml of the element
Range("A1").Value = rating.innerhtml
