VBA-Excel：在 IE 窗口中填写表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40556911/
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
VBA-Excel: Filling forms in an IE Window
提问by WK145
I'm hoping someone can help. I'm trying to speed up the process of filling a webform that must be completed dozens or hundreds of times with information stored in excel.
我希望有人可以提供帮助。我正在尝试加快填写必须使用存储在 excel 中的信息完成数十或数百次的网络表单的过程。
To do this, I need one button to open an IE window and navigate to a certain website's login page (I've figured this bit out). The user can then log in and navigate to the form that needs to be filled. Then, I'd like the user to be able to return to the excel page, click another button, which will automatically fill several drop downs and text boxes.
为此，我需要一个按钮来打开一个 IE 窗口并导航到某个网站的登录页面（我已经弄清楚了这一点）。然后用户可以登录并导航到需要填写的表格。然后，我希望用户能够返回到 Excel 页面，单击另一个按钮，该按钮将自动填充多个下拉列表和文本框。
Within Excel, I already have some code to allow the user to search for the particular set of information that needs to go to the form, so all they should have to do is click the button to transfer it over. The first bit of the code is just this:
在 Excel 中，我已经有了一些代码来允许用户搜索需要转到表单的特定信息集，因此他们应该做的就是单击按钮将其转移。代码的第一部分是这样的：
Public IE As Object
Public Sub OpenIE()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate "loginpage url"
End Sub
Where I'm having trouble, however, is having a different function access the same IE window once the user has logged in and navigated to the form. Right now I've got this:
然而，我遇到问题的地方是，一旦用户登录并导航到表单，就有不同的功能访问同一个 IE 窗口。现在我有这个：
Sub FillMacro()
Dim sh As Object, oWin As Object
Set sh = CreateObject("Shell.Application")
For Each oWin In sh.Windows
If TypeName(oWin.document) = "HTMLDocument" Then
Set IE = oWin
Exit For
End If
Next
IE.Visible = True
Application.Wait (Now + TimeValue("00:00:01"))
IE.document.getElementById("idec").Value = "John"
Application.Wait (Now + TimeValue("00:00:01"))
IE.document.getElementById("idee").Value = "Smith"
End Sub
Most of that I've gotten from other posts on this forum, and while I'm a bit of a novice at this, the problem seems to be that for some reason VBA can't find the text boxes with the id of LastName or FirstName. What's more, the IE.Visible = True doesn't bring the IE window back to the foreground, so I'm trying to find the proper line to do that. When I try to run the code, I get an "Object Required" error at:
我从这个论坛上的其他帖子中获得的大部分内容，虽然我在这方面有点新手，但问题似乎是由于某种原因，VBA 找不到带有姓氏或 ID 的文本框名。更重要的是，IE.Visible = True 不会将 IE 窗口带回前台，所以我试图找到合适的行来做到这一点。当我尝试运行代码时，我在以下位置收到“需要对象”错误：
IE.document.getElementById("idec").Value = "John"
I've tried searching this site and will continue to look, but any help in the meantime would be greatly appreciated!
我试过搜索这个网站并将继续寻找，但在此期间的任何帮助将不胜感激！
On the Internet Explorer page, here is the line for the first text box I'm trying to fill:
在 Internet Explorer 页面上，这是我要填充的第一个文本框的行：
<input name="componentListPanel:componentListView:1:component:patientLastNameContainer:patientLastName" class="input300" id="idec" type="text" maxlength="60" value="">
采纳答案by Ryszard J?draszyk
Why not automate logging process as well? Login could be stored in Excel and its value read by macro from cell.
为什么不自动化日志记录过程呢？登录名可以存储在 Excel 中，其值可以通过宏从单元格读取。
As Tim Williams suggests, if there is Iframe on website, use (for me it works only with contentwindow included):
正如蒂姆威廉姆斯所建议的那样，如果网站上有 Iframe，请使用（对我来说它仅适用于包含 contentwindow）：
IE.document.getElementById("iFrameIdHere").contentwindow.document.getEleme?ntById("idec").Value = "John"
Instead of Application.Waituse:
而不是Application.Wait使用：
Do Until IE.ReadyState = 4 And IE.Busy = False
DoEvents
Loop
It will save you a lot of time when page loads fast and prevent errors when loading exceeds wait time. Use it ONLY after page reloads (meaning after navigating or anything what causes page reloads, especially .clickon HTML elements.
当页面加载速度快时，它将为您节省大量时间，并在加载超过等待时间时防止错误。仅在页面重新加载后使用它（意味着在导航或任何导致页面重新加载的原因之后，尤其是.click在 HTML 元素上。
Use early binding, it's a bit faster than creating objects. It can increase performance by a few percent based on page loading speed, the faster pages load, the bigger increase.
使用早期绑定，它比创建对象快一点。它可以根据页面加载速度提高几个百分点的性能，页面加载速度越快，增幅越大。
Set IE = New InternetExplorer
Finally, you can toggle loading pictures, depending on whether you need to download images from website.
最后，您可以切换加载图片，具体取决于您是否需要从网站下载图片。
Public Sub ShowPictures(ByVal EnabledStatus As Boolean)
Public ScrapingCancelled as Boolean
Dim obj_Shell
Dim v_Result As Variant
Set obj_Shell = CreateObject("WScript.Shell")
'Reads the registry key that determines whether 'Show pictures' Internet Explorer advanced setting is enabled
v_Result = obj_Shell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Display Inline Images")
Select Case v_Result
Case "yes" 'Pictures are displayed
If EnabledStatus = False Then _
obj_Shell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Display Inline Images", "no", "REG_SZ"
Case "no" 'Pictures are not displayed
If EnabledStatus = True Then _
obj_Shell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Display Inline Images", "yes", "REG_SZ"
Case Else
ScrapingCancelled = True
End Select
End Sub
No images loaded:
没有加载图片：
ShowPictures (0)
Images loaded:
图片加载：
ShowPictures (1)
A good practice is to set value to 1 in the end of macro.
一个好的做法是在宏的末尾将值设置为 1。
