VBA-Excel：在IE窗口中填写表单 [英] VBA-Excel: Filling forms in an IE Window

问题描述

我希望有人可以帮忙。我正在加快填写一个webform的过程，必须使用excel中存储的信息完成数十或数百次。

为此，我需要一个按钮来打开一个IE窗口并导航到某个网站的登录页面（我已经弄清楚了这一点）。然后，用户可以登录并导航到需要填写的表单。然后，我希望用户能够返回到excel页面，单击另一个按钮，这将自动填充几个下拉列表和文本框。

Excel内，我已经有一些代码允许用户搜索需要转到表单的特定信息集，所以他们应该做的只是点击按钮将其转移。代码的第一位就是这样：

 公共IE作为对象
 
 Public Sub OpenIE ）
设置IE = CreateObject（InternetExplorer.Application）
 IE.Visible = True 
 IE.Navigateloginpage url
 End Sub 
  

然而，我遇到麻烦的是，一旦用户登录并导航到形成。现在我已经有了：

  Sub FillMacro（）
 
 Dim sh As Object，oWin作为对象
 
设置sh = CreateObject（Shell.Application）
 
对于每个oWin在sh.Windows 
如果TypeName（oWin.document）=HTMLDocument 然后
设置IE = oWin 
退出
结束如果
下一个
 
 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 
  

我从这个论坛的其他帖子中获得的大部分内容，虽然我有点新手，但是问题似乎是由于某种原因VBA找不到文本包含姓氏或名字的框。此外，IE.Visible = True不会使IE窗口回到前台，所以我试图找到正确的行来做到这一点。当我尝试运行代码时，我得到一个对象必需错误：

  IE.document.getElementById（idec ）.Value =John
  

我已经尝试搜索这个网站，并会继续看，但是在此期间的任何帮助将不胜感激！

在Internet Explorer页面上，以下是我要填写的第一个文本框的行： p>

 < input name =componentListPanel：componentListView：1：component：patientLastNameContainer：patientLastNameclass =input300id =idec type =textmaxlength =60value => 
  

解决方案

为什么不自动化日志记录过程？登录可以存储在Excel中，其值由单元格宏读取。

正如蒂姆·威廉姆斯所说，如果网站上有iframe，请使用（对我来说，包含内容窗口）：

  IE.document.getElementById（iFrameIdHere）。contentwindow.document.getElementById（idec）。 Value =John
  

而不是 Application.Wait 使用：

 直到IE.ReadyState = 4而IE.Busy = False 
 DoEvents 
循环
  

当页面加载速度快时，会节省大量时间，并在加载超过等待时间时防止错误。在页面重新加载之后使用它（意味着在导航之后或任何导致页面重新加载的原因，特别是 .click 在HTML元素上。

使用早期绑定，它比创建对象要快一点，它可以根据页面加载速度，加快页面加载速度，增加更多的页面来提高性能。

 设置IE =新的InternetExplorer 
  

最后，您可以切换加载图片，具体取决于是否需要从网站下载图像。

  Public Sub ShowPictures（ByVal EnabledStatus As Boolean）
 
公共ScrapingChecklled为布尔
 Dim obj_Shell 
 Dim v_Result As Variant 
设置obj_Shell = CreateObject（WScript.Shell）
 
'读取注册表确定是否启用显示图片Internet Explorer高级设置的键
 
 v_Result = obj_Shell.RegRead（HKEY_CURRENT_USER\Software\Microsoft\Internet Expl orer\Main\Display Inline Images）
 
选择案例v_Result 
案例是'显示图片
如果EnabledStatus = False则_ 
 obj_Shell。 RegWriteHKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Display Inline Images，no，REG_SZ
no图像不显示
如果EnabledStatus = True Then _ 
 obj_Shell.RegWriteHKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Display Inline Images，yes，REG_SZ
 Case Else 
 ScrapingCancelled = True 
结束选择
 
 End Sub 
  

没有图像加载：

  ShowPictures（0）
  

加载图片

  ShowPictures（1）
  

一个好的做法是在宏结束时将值设置为1。

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.

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.

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:

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:

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:

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:

<input name="componentListPanel:componentListView:1:component:patientLastNameContainer:patientLastName" class="input300" id="idec" type="text" maxlength="60" value="">

解决方案

Why not automate logging process as well? Login could be stored in Excel and its value read by macro from cell.

As Tim Williams suggests, if there is Iframe on website, use (for me it works only with contentwindow included):

IE.document.getElementById("iFrameIdHere").contentwindow.document.getEleme‌ntById("idec").Value = "John"

Instead of Application.Wait use:

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 .click on HTML elements.

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.

这篇关于VBA-Excel：在IE窗口中填写表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！