文章/答案/技术大牛

发布

社区首页 >问答首页 >IE Web自动化-如何使用Excel VBA/XML宏自动选择与单元格匹配的web组合框值

问IE Web自动化-如何使用Excel VBA/XML宏自动选择与单元格匹配的web组合框值
EN

Stack Overflow用户

提问于 2018-05-30 17:40:10

回答 1查看 281关注 0票数 1

我是一个VBA初学者，我正面临着在web组合框中自动选择国家名称的问题，通过循环从Excel表格中使用单元格的值。如果有人能帮我修复我的VBA和XMLHTTP代码，那就太好了。我的工作表和VBA代码如下：

工作表、VBA代码、下面的XML代码

1      PP #           Nationality   DOB           Work Permit Number
2      REDACTED       Indian        03/01/1978    ?
3                                                 ?
4                                                 ?
5                                                 ?


Sub MOLScraping()
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.sheets("MOL")
LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

Dim IE As New InternetExplorer, HTML As HTMLDocument, post As Object, URL$

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

For i = 2 To LastRow

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

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 = sht.Range("C" & i)
                  
    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 = sht.Range("D" & i) 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 = sht.Range("E" & i)
    
    HTML.querySelector("button[onclick='SearchEmployee()']").Click
    
    HTML.getElementById("TransactionInfo_WorkPermitNumber").innerText = sht.Range("G" & i)
    
End With
Next x
End Sub


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

QueryString = "{""PersonPassportNumber"":""REDACTED"",""PersonNationality"":""100"",""PersonBirthDate"":""01/01/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

vba

excel

web-scraping

browser-automation

webautomation

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-05-30 18:59:57

评论：

这是一个使用selenium basic的示例，它应该很容易适应循环，甚至可以为Internet Explorer重写。

如果你愿意，你可以尝试添加明确的等待时间(感谢@Topto提醒我这些时间)。示例如下所示。显式等待、selenium风格似乎不起作用的一个例子是Passport #。在这里，我添加了一个循环，以确保在尝试更新之前显示它。

参考：

selenium basic包装器是免费的。安装后，您可以转到VBE >工具>引用> Selenium类型库

待办事项：

这是为了演示原理。您可以轻松地启动驱动程序，然后让您的循环从工作表中拾取变量并发出新的GET请求。

代码：

Option Explicit

Public Sub MOLScraping()
    'Tools > references > selenium type library

    Dim d As New ChromeDriver                    '<== can change to other supported driver e.g. IE

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

    With d
        .Start
        .Get URL
        .FindElementByCss("button[ng-click='showEmployeeSearch()']").Click

         Do
             DoEvents
         Loop Until .FindElementById("txtPassportNumber").IsDisplayed

        .FindElementById("txtPassportNumber", timeout:=20000).SendKeys "123456"
        .FindElementById("Nationality").SendKeys "ALBANIA"
        .FindElementByCss("td.ng-binding").Click
        .FindElementById("txtBirthDate", timeout:=20000).SendKeys "12/01/20009"
        .FindElementByCss("td.active.day").Click
        .FindElementByCss("button[onclick*='SearchEmployee']").Click

        Stop

        'QUIT
    End With

End Sub

编辑

没有基于selenium的答案(基于您引用的@SIM的答案)

Option Explicit

Public Sub GetData()
    Dim res As Variant, QueryString As String, Permit As Long, Name As String, i As Long

    Dim passportNumber As String, personNationality As Long, birthdate As String

    Dim sht As Worksheet, lastRow As Long
    Set sht = ActiveSheet

    With sht
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With

    For i = 2 To lastRow

        QueryString = "{""PersonPassportNumber"":""" & sht.Cells(i, 3) & """,""PersonNationality"":""" & sht.Cells(i, 4) & """,""PersonBirthDate"":""" & sht.Cells(i, 5) & """}"

        With CreateObject("MSXML2.serverXMLHTTP") 'New XMLHTTP60
            .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
            Debug.Print res
        End With

        Permit = Replace(Split(Split(s, """OtherData"":""")(1), ",")(0), Chr$(34), vbNullString)
        Name = Split(Split(Split(res, "Employees"":")(1), "OtherData2"":""")(1), """}")(0)

        sht.Cells(i, 1) = Permit: sht.Cells(i, 2) = Name
    Next i
End Sub

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/50601368

复制

相似问题

问IE Web自动化-如何使用Excel VBA/XML宏自动选择与单元格匹配的web组合框值
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问IE Web自动化-如何使用Excel VBA/XML宏自动选择与单元格匹配的web组合框值EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问IE Web自动化-如何使用Excel VBA/XML宏自动选择与单元格匹配的web组合框值
EN