IE Web Automation-如何使用Excel VBA/XML宏从组合框中自动选择值 [英] IE Web Automation - How to auto select value from combo box using Excel VBA/XML Macro

问题描述

我是VBA的初学者，但无法从Excel电子表格的Web组合框或列表框中自动选择国家/地区名称.我的代码仅输入国家名称，但未选择. 如何更改此代码，以便它可以从Excel电子表格中选择国家/地区名称，然后在Web组合框中选择相同的国家/地区名称作为循环.护照号码，DOB和国籍在我的代码上正确无误.如果您将手动使用，则可以找到我需要在电子表格中捕获的工作许可证编号.随附了Chrome Inspect Element屏幕截图.

I'm a beginner in VBA and I've failed to select country name automatically in web Combo box or list box from my Excel spreadsheet. My code is entering country name only, but not selecting it. How can I change this code so it can pick country name from my Excel spreadsheet and select the same in web combo box as a loop. Passport number, DOB and Nationality are correct on my code. If you'll use manually then you can find the work permit number which I need to capture in my spreadsheet. Chrome Inspect Element screenshot is attached herewith.

我的代码如下:

Sub MOL()
    Dim IE As New SHDocVw.InternetExplorer
    Dim Doc As MSHTML.HTMLDocument
    Dim Buttons As MSHTML.IHTMLElementCollection
    Dim Button As MSHTML.IHTMLElement
    Dim HTMLInput As MSHTML.IHTMLElement
    Dim Tags As MSHTML.IHTMLElement
    Dim HTMLTables As MSHTML.IHTMLElementCollection
    Dim HTMLTable As MSHTML.IHTMLElement
    Dim HTMLRow As MSHTML.IHTMLElement
    Dim HTMLCell As MSHTML.IHTMLElement
    Dim Alltext As IHTMLElementCollection

Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
'Application.EnableEvents = False

On Error Resume Next

    IE.Visible = True
    IE.navigate "https://eservices.mol.gov.ae/SmartTasheel/Complain/IndexLogin?lang=en-gb"

Do While IE.readyState <> READYSTATE_COMPLETE: Loop

Set Doc = IE.document
Set Buttons = Doc.getElementsByTagName("Button")
Buttons(2).Click
Do While IE.readyState <> READYSTATE_INTERACTIVE = 3: Loop
Set HTMLInputs = Doc.getElementsByTagName("Input")
    HTMLInputs(46).Value = "somevalue"
    HTMLInputs(48).Value = "24/02/1990"
    HTMLInputs(47).Value = "India"
Buttons(21).Click
End Sub

推荐答案

您要寻找的解决方案很难提供.从下拉菜单中选择NATIONALITY的过程很困难.我在脚本中使用了.querySelector()使其简洁.但是，无论您要从下拉菜单中选择哪个国家/地区，它都应满足您的目的.试一试:

The solution you look for is a bit difficult to provide. There are few tricky parts to hurdle to select the NATIONALITY from dropdown. I've used .querySelector() within the script to make it concise. However, it should serve your purpose no matter whatever country you wanna select from dropdown. Give it a shot:

Sub GetInfo()
    Dim IE As New InternetExplorer, HTML As HTMLDocument, post As Object, URL$

    URL = "https://eservices.mol.gov.ae/SmartTasheel/Complain/IndexLogin?lang=en-gb"

    With IE
        .Visible = True
        .navigate URL
        While .Busy = True Or .readyState < 4: DoEvents: Wend
        Set HTML = .document

        HTML.getElementById("TransactionInfo_WorkPermitNumber").innerText = "2659558"
        HTML.querySelector("button[ng-click='showEmployeeSearch()']").Click

        Application.Wait Now + TimeValue("00:00:03")  ''If for some reason the script fails, make sure to increase the delay

        HTML.getElementById("txtPassportNumber").Value = "J2659558"
        HTML.getElementById("Nationality").Focus
        For Each post In HTML.getElementsByClassName("ng-scope")
            With post.getElementsByClassName("ng-binding")
                For I = 0 To .Length - 1
                    If .item(I).innerText = "INDIA" Then ''you can change the country name here to select from dropdown
                        .item(I).Click
                        Exit For
                    End If
                Next I
            End With
        Next post
        HTML.getElementById("txtBirthDate").Value = "24/02/1990"
        HTML.querySelector("button[onclick='SearchEmployee()']").Click
    End With
End Sub

要添加到库中的引用:

Microsoft Internet Controls
Microsoft HTML Object library

执行上述脚本时，它应该会为您提供所需的结果.

When you execute the above script, it should give you the desired result.

另一种方法是使用xmlhttp请求，该请求比IE更快.您需要通过"POST"请求将query string parameter参数作为字典传递.如果要像在birth date，passport或nationality中那样更改参数，只需在QueryString中进行设置.顺便说一句，Nationality参数应该用value而不是name填充，而100代表INDIA..这就是脚本的样子:

Another way would be to go for using xmlhttp request which is way faster than IE. You need to pass the query string parameter arguments as dictionary through "POST" request. If you want to change the parameter as in, birth date,passportor nationality just do it in the QueryString. Btw, the Nationality parameter should be filled in with value instead of name as in, 100 for INDIA. This is how your script should look like:

Sub Get_Data()
    Dim res As Variant, QueryString$, ID$, Name$

    QueryString = "{""PersonPassportNumber"":""J2659558"",""PersonNationality"":""100"",""PersonBirthDate"":""24/02/1990""}"

    With New XMLHTTP
        .Open "POST", "https://eservices.mol.gov.ae/SmartTasheel/Dashboard/GetEmployees", False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .setRequestHeader "Content-Type", "application/json"
        .send QueryString
        res = .responseText
    End With

    ID = Split(Split(Split(res, "Employees"":")(1), "ID"":""")(1), """,")(0)
    Name = Split(Split(Split(res, "Employees"":")(1), "OtherData2"":""")(1), """}")(0)

    [A1] = ID: [B1] = Name
End Sub

要添加到库中的引用:

Microsoft XML, V6.0

运行上述脚本，您应该获得所需搜索的NAME和ID.

Running the above script, you should get the NAME and ID of your required search.

这篇关于IE Web Automation-如何使用Excel VBA/XML宏从组合框中自动选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！