Html 使用 VBA 从 Web 文档输入元素中获取价值

Get value from web document input element with VBA

提问by pexpex223

I am having difficult to retrieve value 300from the input named points.

我很难300从名为points.

Here's my HTML and VBA code.

这是我的 HTML 和 VBA 代码。

HTML:

HTML:

<td id="myPower_val_9" style="visibility: visible;">
    <input type="text" disabled="disabled" value="300" name="points"></input>
</td>

VBA:

VBA:

Dim ie As Object
Dim myPoints As String

Set ie = CreateObject("InternetExplorer.Application")

With ie
  .Visible = 0
  .navigate "www.example.com"

   While .Busy Or .readyState <> 4 
   DoEvents
   Wend

End With

Dim Doc As HTMLDocument
Set Doc = ie.document

myPoints = Trim(Doc.getElementsByTagName("td")(0).getElementById("myPoints").innerText)
Range("A1").Value = myPoints 

回答by KyleMit

HTML Code

HTML代码

I'd try working out the code that manipulates the Document Object Model (DOM) in javascript in a web browser so you can make use of better web based debugging tools.

我会尝试在 Web 浏览器中的 javascript 中编写操作文档对象模型 (DOM) 的代码，以便您可以使用更好的基于 Web 的调试工具。

There are several issues herethat a console or debugger could help out with:

控制台或调试器可以帮助解决以下几个问题：

• You want to get the element ID myPointsbut in HTML it's just called points
• You want to get the element by ID, but you've only set the name property -
• As long as nameis unique to the element, you don't need to search for a tdfirst
• As you can see from <input></input>, input elements do not have innerText(the text inside the ><). Instead they have a valueattribute
• The element exposes it's attributes and other data through the properties on the object itself. So you can check the input's value by just looking at .value

• 您想获取元素 ID，myPoints但在 HTML 中它只是被调用points
• 您想通过 ID 获取元素，但您只设置了 name 属性 -
• 只要name是独一无二的元素，你不需要搜索td第一
• 从 中可以看出<input></input>，输入元素没有innerText（ 中的文本><）。相反，他们有一个value属性
• 该元素通过对象本身的属性公开其属性和其他数据。因此，您只需查看即可检查输入的值.value

Here's a javascript example of what you're trying to do:

这是您要执行的操作的 javascript 示例：

var value = document.getElementsByName("points")[0].value;
console.log(value);

<input type="text" disabled="disabled" value="300" name="points" />
    

Open the console (F12), and you should see 300

打开控制台 ( F12)，您应该会看到300

VBA

VBA

To convert it to VBA code for Excel, just make sure you uses parentheses ()for VB arrays instead of square brackets []for JS arrays:

要将其转换为 Excel 的 VBA 代码，只需确保()对 VB 数组使用括号而不是[]对 JS 数组使用方括号：

myPoints = Trim(Doc.getElementsByName("points")(0).Value)

That should work just fine.

那应该工作得很好。

References

参考

Since I'm not sure at what point you're failing in VB, also make sure you have all the proper web references in place in your VBA script.

由于我不确定您在 VB 中的哪一点失败，还要确保您在 VBA 脚本中拥有所有正确的 Web 引用。

Go to Tools > References > and add "Microsoft HTML Object Library" and "Microsoft Internet Controls":

转到工具 > 参考 > 并添加“Microsoft HTML 对象库”和“Microsoft Internet 控件”：

Demo

演示

I created a demo in plunkerso there would be a live site to go against instead of example.com.

我在 plunker 中创建了一个演示，因此将有一个实时站点而不是 example.com。

Paste the following code into excel and everything should work fine:

将以下代码粘贴到 excel 中，一切正常：

Public Sub GetValueFromBrowser()
    Dim ie As Object
    Dim url As String
    Dim myPoints As String

    url = "http://run.plnkr.co/plunks/6UTb9kHRZ363Ivhh2BPE/"
    Set ie = CreateObject("InternetExplorer.Application")

    With ie
      .Visible = 0
      .navigate url
       While .Busy Or .readyState <> 4
         DoEvents
       Wend
    End With

    Dim Doc As HTMLDocument
    Set Doc = ie.document

    myPoints = Trim(Doc.getElementsByName("points")(0).Value)
    Range("A1").Value = myPoints

End Sub

Output:

输出：

回答by QHarr

CSS selector:

CSS 选择器：

Use a CSS selector to get the element of input[name='points']

使用 CSS 选择器获取元素 input[name='points']

You don't show enough HTML to know if this is the only on the page. The above says elements with inputtag having attribute namewhose value is 'points'

您没有显示足够的 HTML 来知道这是否是页面上唯一的。上面说带有input标签的元素的属性name值为'points'

---

---

CSS query:

CSS查询：

---

---

VBA:

VBA：

You apply the CSS selector with .querySelectormethod of documentfor a single element; .querySelectorAllfor a nodeListof all matching elements i.e. if there is more than one on the page and you get the one of interest by index.

您可以使用.querySelector方法document为单个元素应用 CSS 选择器；.querySelectorAll对于nodeList所有匹配元素中的一个，即如果页面上有多个元素并且您通过索引获得感兴趣的元素。

Debug.Print ie.document.querySelector("input[name='points']").getAttribute("value")

回答by Hubvill

You need to use .getAttribute("name of attribute")to get an attributes value. In your case .getAttribute("value")will return 300.

您需要使用.getAttribute("name of attribute")来获取属性值。在你的情况下.getAttribute("value")将返回 300。

Dim ie As Object
Dim myPoints As String

Set ie = CreateObject("InternetExplorer.Application")

With ie
  .Visible = 1
  .navigate "website URL"

   While .Busy Or .readyState <> 4
   DoEvents
   Wend

End With

Dim Doc As HTMLDocument
Set Doc = ie.document

myPoints = Trim(Doc.getElementsByTagName("td")(0).getElementsByTagName("input")(0).getAttribute("value"))
Range("A1").Value = myPoints

Just on a side note. I don't know much about HTML and maybe someone can elaborate on this more. But if you want to test that HTML code you need to add in the < table> < tr> tags.
Something like this:

只是在旁注。我对 HTML 了解不多，也许有人可以详细说明这一点。但是如果你想测试那个 HTML 代码，你需要在 <table> <tr> 标签中添加。
像这样的东西：

<table>
    <tr>
        <td id="myPower_val_9" style="visibility: visible;">
            <input type="text" disabled="disabled" value="300" name="points"></input>
        </td>
    </tr>
</table>