VBA代码根据Excel工作簿中的值从webform下拉菜单中选择一个值 [英] VBA code to select a value from webform dropdown based on value in excel workbook

问题描述

我需要根据我写宏的Excel书中的值从webform下拉列表中选择一个值。到目前为止，我已能够导航到该网站，并使用以下vba代码单击所需的选项卡：

  Sub FillInternetForm ）
 Dim ie As Object 
 Set ie = CreateObject（InternetExplorer.Application）
 
 ie.Navigatewebsite我正在浏览
 ie.Visible = True 
 
虽然ie.Busy 
 DoEvents'等到IE完成加载页面。 
 WEND 
 
将AllHyperLinks = ie.Document.getElementsByTagName（ A）
。对于每一个Hyper_link在AllHyperLinks 
。如果Hyper_link.innerText = 对帐 那么B $ $ b Hyper_link.Click 
退出For 
 End If 
 Next 
  

接下来，我需要基于工作簿中的预定义值（单元格引用）单击准备工具，批准者或审阅者，我在此尝试编写宏。下面是html编码，我相信我需要在我的宏中引用它来执行所描述的操作：

 < td class = DashControlTableCellRight>< select name =ctl00 $ MainContent $ ucDashboardPreparer $ ucDashboardSettings $ ctl00 $ ddlRolesclass =ControlDropDownid =ctl00_MainContent_ucDashboardPreparer_ucDashboardSettings_ctl00_ddlRolesonchange =OnRoleChanged（this）;> 
< option selected =selectedvalue =Preparer> Preparer< / option> 
< option value =Reviewer> Reviewer< / option> 
< option value =批准者>批准者< / option> 
 
< / select>< / td> 
  

任何帮助都将不胜感激。

最好，

格兰特

解决方案

我首先想指出使用 ie.busy 本身是危险的。 .busy 在自动化网页时非常不可靠，我建议您也包含 .readyState 属性在你的循环中。

看到这个测试我用 .readyState使用循环运行< 4 ：

请注意， .Busy 对于第一个5行，然后在第6行变成虚假？这是您的代码认为该网页已加载的位置。但是， .readyState 仍然是1 （相当于 READYSTATE_LOADING ） p>

突然之间它再次变得繁忙，直到 .readystate = 4 （ READYSTATE_COMPLETE

我已将您的 .busy 方法移入因为这是在浏览网页时经常调用的东西。

  Sub ieBusy（即As Object）
 Do虽然ie.busy或ie.readystate< 4 
 DoEvents 
 Loop 
 End Sub 
 
 Sub FillInternetForm（）
 Dim ie As Object 
 Set ie = CreateObject（InternetExplorer.Application ）
 
 ie.navigate网站我正在导航到
 ie.Visible = True 
 
 iebusy ie 
 
 Set AllHyperLinks = ie.document.getElementsByTagName（ A）
。对于每一个Hyper_link在AllHyperLinks 
。如果Hyper_link.innerText = 对帐 然后
 Hyper_link.Click 
退出对于
 end如果
下一
 
 iebusy即
 
尺寸mySelection作为字符串，mySelObj作为对象
将mySelObj = ie.document.getElementById（ ctl00_MainContent_ucDashboardPreparer_ucDashboardSettings_ctl00_ddlRoles） 
 
'在这里设置您的选择
 mySelection =Preparer'或Reviewer或Approver 
选择案例mySelection 
案例Preparer，Reviewer，Approver 
 Case Else 
 MsgBox 无效的选择！ 
 ie.Quit 
 Exit Sub 
 End Select 
 
 mySelObj.Value = mySelection 
 
 End Sub 
  

mySelObj 是设置您的选择的对象。

I'm needing to select a value from a webform dropdown based on the value in the excel book where I'm writing the macro. So far I've been able to navigate to the website and click the desired tab with the following vba code:

Sub FillInternetForm()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

ie.Navigate "website I'm navigating to"
ie.Visible = True

While ie.Busy
DoEvents 'wait until IE is done loading page.
Wend

Set AllHyperLinks = ie.Document.getElementsByTagName("A")
    For Each Hyper_link In AllHyperLinks
        If Hyper_link.innerText = "Reconciliations" Then
            Hyper_link.Click
            Exit For
        End If
    Next

Next, I'm needing to click either "Preparer", "Approver", or "Reviewer" based on a predefined value (cell reference) within the workbook where I'm attempting to write the macro. Below is the html coding that I believe I need to reference within my macro to perform the action described:

<td class="DashControlTableCellRight"><select name="ctl00$MainContent$ucDashboardPreparer$ucDashboardSettings$ctl00$ddlRoles" class="ControlDropDown" id="ctl00_MainContent_ucDashboardPreparer_ucDashboardSettings_ctl00_ddlRoles" onchange="OnRoleChanged(this);">
<option selected="selected" value="Preparer">Preparer</option>
<option value="Reviewer">Reviewer</option>
<option value="Approver">Approver</option>

</select></td>

Any help would be greatly appreciated.

Best,

Grant

解决方案

I first want to point out that using ie.busy by itself is dangerous. .busy is very unreliable when you are automating web pages, and I would recommend that you also include the .readyState property in your loop.

See this test I ran using a loop using .readyState < 4:

Notice how .Busy was true for the first 5 lines, then became false on line 6? This is where your code would have thought the webpage was loaded. However, .readyState was still 1 (which is the equivalent to READYSTATE_LOADING)

All of a sudden it became busy again until .readystate = 4 (READYSTATE_COMPLETE).

I have moved your .busy method into a separate sub because this is something that is called quite often when navigating web pages.

Sub ieBusy(ie As Object)
    Do While ie.busy Or ie.readystate < 4
        DoEvents
    Loop
End Sub

Sub FillInternetForm()
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")

    ie.navigate "website I'm navigating to"
    ie.Visible = True

    iebusy ie

    Set AllHyperLinks = ie.document.getElementsByTagName("A")
    For Each Hyper_link In AllHyperLinks
        If Hyper_link.innerText = "Reconciliations" Then
            Hyper_link.Click
            Exit For
        End If
    Next

    iebusy ie

    Dim mySelection As String, mySelObj As Object
    Set mySelObj = ie.document.getElementById("ctl00_MainContent_ucDashboardPreparer_ucDashboardSettings_ctl00_ddlRoles")

    'set your selection here
    mySelection = "Preparer"    'or Reviewer    or Approver
    Select Case mySelection
    Case "Preparer", "Reviewer", "Approver"
    Case Else
        MsgBox "Invalid Selection!"
        ie.Quit
        Exit Sub
    End Select

    mySelObj.Value = mySelection

End Sub

The mySelObj is the object that will set your selection.

这篇关于VBA代码根据Excel工作簿中的值从webform下拉菜单中选择一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！