Excel VBA：如何从 Internet Explorer 复制字段的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28201443/
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
Excel VBA: How to copy value of a field from Internet Explorer
提问by excelguigui11
I'm trying to access a form on internet explorer, then copy value of some fields (that I know there name and id) and the copy those value in a an Excel sheet. That's the simplify version of what I'm trying to do. So right now I'm able to open my web page, then open the subform that is included in it. But when I get to copying the value of the field I'm interested in. I'm not able to set the variable so I can then copy it back to Excel.
Here's my code below. My problem between comments.
我正在尝试访问 Internet Explorer 上的表单，然后复制某些字段的值（我知道名称和 ID）并将这些值复制到 Excel 工作表中。这是我正在尝试做的简化版本。所以现在我可以打开我的网页，然后打开其中包含的子表单。但是当我开始复制我感兴趣的字段的值时。我无法设置变量，所以我可以将它复制回 Excel。
下面是我的代码。我的评论之间的问题。
Private Sub Open_multiple_sub_pages_from_main_page()
Dim i As Long
Dim IE As Object
Dim Doc As Object
Dim objElement As Object
Dim objCollection As Object
' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
' You can uncoment Next line To see form results
IE.Visible = True
' Send the form data To URL As POST binary request
IE.navigate "http://webpage.com/"
' Wait while IE loading...
While IE.Busy
DoEvents
Wend
Set objCollection = IE.Document.getElementsByTagName("input")
i = 0
While i < objCollection.Length
If objCollection(i).Name = "txtUserName" Then
' Set text for search
objCollection(i).Value = "1234"
End If
If objCollection(i).Name = "txtPwd" Then
' Set text for search
objCollection(i).Value = "password"
End If
If objCollection(i).Type = "submit" And objCollection(i).Name = "btnSubmit" Then ' submit button if found and set
Set objElement = objCollection(i)
End If
i = i + 1
Wend
objElement.Click ' click button to load page
' Wait while IE re-loading...
While IE.Busy
DoEvents
Wend
' Show IE
IE.Visible = True
Set Doc = IE.Document
Dim links, link, value_to_copy
Dim j As Integer 'variable to count items
j = 0
Set links = IE.Document.getelementbyId("dgTime").getElementsByTagName("a")
n = links.Length
While j <= n 'loop to go thru all "a" item so it loads next page
links(j).Click
While IE.Busy
DoEvents
Wend
'------------THE PROBLEM IS HERE---------------------------
Set value_to_copy = IE.Document.getelementbyId("mainTable").getElementsByTagName("txtProject").innerText
'----------VALUE_TO_COPY WILL REMAIN AT "" VALUE
IE.Document.getelementbyId("DetailToolbar1_lnkBtnSave").Click 'save
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now) 'wait
Loop
IE.Document.getelementbyId("DetailToolbar1_lnkBtnCancel").Click 'close
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now) 'wait
Loop
Set links = IE.Document.getelementbyId("dgTime").getElementsByTagName("a")
j = j + 2
Wend
End Sub
Below is the html code of the page from wich I'm trying to retrieve the "txtProject" value. In that particular case the value is "0000001". That's the value I need to copy.
下面是我试图检索“txtProject”值的页面的 html 代码。在这种特殊情况下，该值为“0000001”。这就是我需要复制的值。
<table width="100%" class="Form" id="mainTable" border="0" cellspacing="0" cellpadding="0">
<tbody><tr id="TRCustomer">
<td class="titleLabel"><span id="lblCustomer">
<u>C</u>lient : </span></td>
<td><input name="txtCustomer" id="txtCustomer" accesskey="C"
language="javascript" onchange="__doPostBack('txtCustomer','')"
type="text"></td>
<td><a class="Button" id="lnkBtnCustomer" href="javascript:__doPostBack('lnkBtnCustomer','')">
<img id="imgCustomer" alt="" src="images/toolbar/b_preview.gif"
border="0"></a></td>
<td class="tdDescriptionLabel"><span class="DescriptionLabel"
id="lblCustomerDescription"> </span></td>
</tr>
<tr id="TRProject">
<td width="110" class="titleLabel">
<span id="lblProject">Pro<u>j</u>et : </span></td>
<td width="152"><input name="txtProject" tabindex="2" id="txtProject"
accesskey="J" language="javascript"
onkeypress="return LookupButton(event,'lnkBtnProject')"
onchange="__doPostBack('txtProject','')" type="text"
value="0000001"></td>
<td width="20"><a class="Button" id="lnkBtnProject"
href="javascript:__doPostBack('lnkBtnProject','')">
<img id="imgProject" alt="" src="images/toolbar/b_preview.gif"
border="0"></a></td><td class="tdDescriptionLabel">
<span class="DescriptionLabel"
Any help will be greatly appreciated.
Tx in advance.
任何帮助将不胜感激。
提前发送。
回答by Tim Williams
Within a document idshould be unique, so you can use that directly:
在一个文档中id应该是唯一的，所以你可以直接使用它：
value_to_copy = IE.Document.getelementbyId("txtProject").Value
getElementsByTagName()isn't used to locate elements by id, and in any case returns a collection of matches, not a single element, so you can't assign it's return value like that. This would be how you'd typically use it:
getElementsByTagName()不用于按 id 定位元素，并且在任何情况下都返回匹配的集合，而不是单个元素，因此您不能像那样分配它的返回值。这将是您通常使用它的方式：
value_to_copy = IE.Document.getelementsbyTagName("input")(0).Value
回答by tpkaplan
This is a subtle problem. In a nutshell, you shouldn't be using the innertextproperty. Instead, you want to retrieve the value of the "value" nodereturned by the childNodesproperty of the txtProject element.
这是一个微妙的问题。简而言之，您不应该使用该innertext属性。相反，您希望检索txtProjectnode的childNodes属性返回的“值”的值element。
The data you are retrieving is not actually "innertext." Innertext would be the text that appears between the opening and closing tags. From MSDN https://msdn.microsoft.com/en-us/library/ie/ms533899(v=vs.85).aspx:
您正在检索的数据实际上并不是“内部文本”。内文是出现在开始和结束标签之间的文本。从 MSDN https://msdn.microsoft.com/en-us/library/ie/ms533899(v=vs.85).aspx：
The innerText property is valid for block elements only. By definition, elements that do not have both an opening and closing tag cannot have an innerText property.
The innerText property is valid for block elements only. By definition, elements that do not have both an opening and closing tag cannot have an innerText property.
For example, in the following snippet:
例如，在以下代码段中：
<h1>Header Text</h1>
The innertext of the <h1>tag is "Header text"
在将InnerText<h1>标签是“头文字”
In your snippet, the tag does not actually have any inner text. You have a td element with attributes but nothing between the <td...>and the </td>:
在您的代码段中，标签实际上没有任何内部文本。您有一个带有属性的 td 元素，但在<td...>和之间没有任何内容</td>：
<td width="152"><input name="txtProject" tabindex="2" id="txtProject"
accesskey="J" language="javascript"
onkeypress="return LookupButton(event,'lnkBtnProject')"
onchange="__doPostBack('txtProject','')" type="text"
value="0000001"></td>'
That's why the innertext property fails. Your snippet, getElementsByTagName("txtProject")returns an Elementobject. Elements are collections of nodes. Your element has nodes named name, tabindex, id, accesskey, language, onkeypress, onchange, typeand value. Each of these nodes has a value associated with it. You need to access the valuenode and query its value.
这就是 innertext 属性失败的原因。您的代码段getElementsByTagName("txtProject")返回一个Element对象。元素是 的集合 nodes。你的元素已命名的节点name，tabindex，id，accesskey，language，onkeypress，onchange，type和value。这些节点中的每一个都有一个与之关联的值。您需要访问该value节点并查询其值。
I suspect the following will work, or something like it.
我怀疑以下内容会起作用，或者类似的东西。
set txtProject = IE.Document.getelementbyID("mainTable").getElementsByTagName("txtProject")
' returns a `<td..></td>` element
value_to_copy = txtProject.childNodes("value")
'Should return the default property of the node "value".
'The default property should be the string "00000001".
'But I'm not sure so you might want to do more
'research on `childNodes` and `nodes`
or alternatively, but less intuitive:
或者，但不太直观：
value_to_copy = IE.Document.getelementbyID("mainTable") _
.getElementsByTagName("txtProject") _
.childNodes("value")
I adapted this from the following code snippet on https://msdn.microsoft.com/en-us/library/ms757053(v=vs.85).aspx. So if my suggestion above doesn't work, maybe this will help.
我从https://msdn.microsoft.com/en-us/library/ms757053(v=vs.85).aspx上的以下代码片段改编了这个。所以如果我上面的建议不起作用，也许这会有所帮助。
The following script example uses the childNodes property (collection) to return an IXMLDOMNodeList, and then iterates through the collection, displaying the value of each item's xml property.
The following script example uses the childNodes property (collection) to return an IXMLDOMNodeList, and then iterates through the collection, displaying the value of each item's xml property.
root = xmlDoc.documentElement;
oNodeList = root.childNodes;
for (var i=0; i<oNodeList.length; i++) {
Item = oNodeList.item(i);
WScript.Echo(Item.xml);
}
回答by Jeanno
The Setkeyword is used for objects, you have here a variable. Drop Setfrom assignment statement. value_to_copy = IE.Document.getelementbyId("mainTable").getElementsByTagName("txtProject").innerText
该Set关键字用于对象，你在这里有一个变量。Set从赋值语句中删除。value_to_copy = IE.Document.getelementbyId("mainTable").getElementsByTagName("txtProject").innerText
回答by Matteo NNZ
The innerTextproperty returns a Stringtype. Which means, you don't need the keyword Setwhich is instead used to set a variable as a pointer to a particular object. Strings are native types, so don't use the keyword Set. Try this instead:
该innerText属性返回一个String类型。这意味着，您不需要Set用于将变量设置为指向特定对象的指针的关键字。字符串是原生类型，所以不要使用关键字 Set。试试这个：
'------------THE PROBLEM IS HERE---------------------------
value_to_copy = IE.Document.getelementbyId("mainTable").getElementsByTagName("txtProject").innerText
'----------VALUE_TO_COPY WILL REMAIN AT "" VALUE
