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 toREADYSTATE_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屋!