gpt4 book ai didi

excel - 通过 Excel 自动化 IE 以填写下拉列表并继续

转载 作者:行者123 更新时间:2023-12-04 16:39:43 24 4
gpt4 key购买 nike

诚然,我仍然是一个通过 Excel 自动化 IE 的新手,但我将不胜感激任何人可以为此提供的任何帮助。基本上,这会打开一个网页,选择一个按钮,填写 2 个下拉菜单,输入一个值,然后按下另一个按钮来显示我需要的内容。我确实使用带有一堆 {Tabs}、{`}、{Down} 等的 SendKeys 来完成这项工作,但它相当笨重。我宁愿以正确的方式执行此操作,但我只能进入第一个下拉菜单,选择我需要的值,然后它停止。我想我缺少的是告诉 IE 接受我输入的内容并继续。编码如下。包含评论以显示它在做什么以及它在何处停止。

Dim WebIDStr As String: WebIDStr = "CD00003630"
Dim IE As Object
WebNavStr = "https://a810-dobnow.nyc.gov/Publish/#!/"
On Error Resume Next
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.navigate WebNavStr
Do Until .readyState = 4: DoEvents: Loop
End With
' Won't work without a delay??
Application.Wait (Now + TimeValue("00:00:03"))
IE.document.getElementsByClassName("white ng-scope")(3).Click
' the next command works and fills-in the dropdown with the value
' that I need but then locks up. Can't move on from here.
IE.document.getElementById("DeviceOptions").selectedIndex = 4
' GOOD to HERE. Tried the next 2 lines but they don't do anything, unfortunately
IE.document.getElementById("DeviceOptions").Focus
IE.document.getElementById("DeviceOptions").Click
' This is where I need to get to. Next Dropdown Value = 1
IE.document.getElementById("craneDeviceOption").selectedIndex = 1
' Once 2nd dropdown selected, fill in "DevCraneID" box
IE.document.getElementById("DevCraneID").Value = WebIDStr
' Press the "Select" button
IE.document.getElementById("Search4")(0).Click
' IE.Quit
' Set IE = Nothing

最佳答案

好的,因为你写的是你想了解它是如何工作的,所以我已经详细地注释了整个代码。

这是工作代码:

Sub DeviceSearch()

'Define constants
Const url As String = "https://a810-dobnow.nyc.gov/Publish/#!/"

'Declare variables
Dim ie As Object
Dim htmlDoc As Object
Dim nodeDeviceTypeDropdown As Object
Dim nodeCraneDeviceDropdown As Object
Dim nodeCraneDeviceID As Object

Dim searchTerm As String

'Initialize variables
searchTerm = "CD00003630" 'craneID

'Initialize Internet Explorer, set visibility,
'call URL and wait until page is fully loaded
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate url
Do Until ie.readyState = 4: DoEvents: Loop
'Wait to load dynamic content after IE reports it's ready
Application.Wait (Now + TimeSerial(0, 0, 3))
'Shorten html document string for lazy coders ;-)
'Seriously: You can of course also use "With ie.document"
Set htmlDoc = ie.document

'Open the Device Search section
htmlDoc.getElementsByClassName("white ng-scope")(3).Click

'Try to get the first dropdown.
'Never use "On Error Resume Next" for the whole code.
'We use it here because if an html id can't be found
'a runtime error occours. But after the critical part
'we switch the error detection back on with "On Error GoTo 0"
'(I use this here only to show you what to do if not sure if
'you can get an element by id. In this case it's not realy
'requiered because we can assume the dropdown is present.)
On Error Resume Next
Set nodeDeviceTypeDropdown = htmlDoc.getElementById("DeviceOptions")
On Error GoTo 0

'Now we can check if the dropdown element was found
'If an object variable has no value it is "Nothing"
'To check if it has a value we must check if it's
'"Not" "Nothing"
'You can use this mechanism for every object variable
'in VBA
If Not nodeDeviceTypeDropdown Is Nothing Then
'Select the wanted dropdown entry
nodeDeviceTypeDropdown.selectedIndex = 4

'To make the selection work you must trigger the
'html change event of the dropdown
Call TriggerEvent(htmlDoc, nodeDeviceTypeDropdown, "change")

'Give time to generate the code for the second dropdown
Application.Wait (Now + TimeSerial(0, 0, 1))
Else
'Dropdown not found
MsgBox "The Dropdown for Device Search was not found"
'Stop makro
Exit Sub
End If

'Here we can use the second dropdown "Search Crane Device"
'We do it from here without error handling
Set nodeCraneDeviceDropdown = htmlDoc.getElementById("craneDeviceOption")
'Select the wanted dropdown entry
nodeCraneDeviceDropdown.selectedIndex = 1
'Trigger the change event of this dropdown
Call TriggerEvent(htmlDoc, nodeCraneDeviceDropdown, "change")
'Give time to generate the code for the text field
Application.Wait (Now + TimeSerial(0, 0, 1))

'Now we have the text field present and can enter the search term (craneID)
'Get the html input element
Set nodeCraneDeviceID = htmlDoc.getElementById("DevCraneDeviceID")
'
'It is not enough to enter the ID. The input field also has html events
'that must be triggered so that the entered value is not only displayed
'but also taken over to submit.
'We have to embed the entering of the crane id in the both events
'"compositionstart" and "compositionend"
Call TriggerEvent(htmlDoc, nodeCraneDeviceID, "compositionstart")
nodeCraneDeviceID.Value = searchTerm
Call TriggerEvent(htmlDoc, nodeCraneDeviceID, "compositionend")

'Click the submit button
htmlDoc.getElementById("search4").Click

'Give time to load the result page
Application.Wait (Now + TimeSerial(0, 0, 5))

'Do here what you want with the result
'...
End Sub

这是触发html事件的程序

Private Sub TriggerEvent(htmlDocument As Object, htmlElementWithEvent As Object, eventType As String)

Dim theEvent As Object

htmlElementWithEvent.Focus
Set theEvent = htmlDocument.createEvent("HTMLEvents")
theEvent.initEvent eventType, True, False
htmlElementWithEvent.dispatchEvent theEvent
End Sub

这是 FireFox html 检查器的两个屏幕截图,其中包含元素的事件
如果您不知道需要哪些事件,您必须尝试直到它起作用;-)

页面上使用的下拉菜单的所有事件 Bild dropdownevents89jwk.jpg auf abload.de

页面上使用的输入字段的事件 Bild inputevents8bjf7.jpg auf abload.de

关于excel - 通过 Excel 自动化 IE 以填写下拉列表并继续,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/63294113/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com