使用Excel VBA按类名查询div元素 [英] Query div element by class name using Excel VBA

问题描述

 < p>我正在尝试使用具有特定类名称的div元素的Excel获取数据。 ; div class =myClass> 
< span> Text1< / span> 
< span> Text2< / span> 
< / div> 
  

我的VBA代码：

  Sub GetData（）
 Dim oHtml As HTMLDocument 
 Dim oElement As Object 
 
设置oHtml =新的HTMLDocument 
 
使用CreateObject （WINHTTP.WinHTTPRequest.5.1）
。打开GET，http://www.example.com/，False 
 .send 
 oHtml.body.innerHTML =。 responseText 
结束
 
对于每个oElement在oHtml.getElementsByClassName（myClass）
 Debug.Print oElement.Children（0）.src 
下一个oElement 
 End Sub 
  

这是返回错误：
运行时错误：'438 '：Object不支持此属性或方法。错误在线Debug.Print ...

我已经激活了以下引用：
Microsoft HTML对象库
Microsoft Internet Controls

我想要能够在第一个或第二个跨度上选择文本，并将其粘贴到单元格上，我该怎么做？

解决方案

这对我有用：

  Dim oHtml As HTMLDocument 
 Dim oElement As Object 
 
设置oHtml =新的HTMLDocument 
 
使用CreateObject（WINHTTP.WinHTTPRequest.5.1）
。打开GET， http://www.example.com，False 
 .send 
 oHtml.body.innerHTML = .responseText 
结束
 
设置dados = oHtml.getElementsByClassName （myClass）（0）.getElementsByTagName（span）
 
i = 0 
对于每个oElement在dados 
表（Sheet1）。范围（A & i + 1）= dados（i）.innerText 
i = i + 1 
下一个oElement 
  

I'm trying to get the data to Excel of a div element with a specific class name, like:

<div class="myClass">
   <span>Text1</span>
   <span>Text2</span>
</div>

My VBA code:

Sub GetData()
Dim oHtml       As HTMLDocument
Dim oElement    As Object

Set oHtml = New HTMLDocument

With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "http://www.example.com/", False
    .send
    oHtml.body.innerHTML = .responseText
End With

For Each oElement In oHtml.getElementsByClassName("myClass")
    Debug.Print oElement.Children(0).src
Next oElement
End Sub

This is returning the error: Run-time error: '438': Object doesn't support this property or method. The error is on line Debug.Print ...

I have activated the following refereces: Microsoft HTML Object Library Microsoft Internet Controls

I want to be able to select the text on the first or second span and paste it on a cell, how can I do this?

解决方案

This worked for me:

Dim oHtml As HTMLDocument
Dim oElement As Object

Set oHtml = New HTMLDocument

With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "http://www.example.com", False
    .send
    oHtml.body.innerHTML = .responseText
End With

Set dados = oHtml.getElementsByClassName("myClass")(0).getElementsByTagName("span")

i = 0
For Each oElement In dados
    Sheets("Sheet1").Range("A" & i + 1) = dados(i).innerText
    i = i + 1
Next oElement

这篇关于使用Excel VBA按类名查询div元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！