html - 如果同一行中的单元格值等于 Excel 工作簿中的值，如何单击 HTML 表中的 href

Question

我正在尝试根据 Excel 模板中的值自动填充网络表单。我已经成功地能够访问该站点并使用下面的 VBA 代码单击几下进行导航。接下来，我需要处理一个 HTML 表格。如果 HTML 表格同一行中的单元格等于我的 Excel 工作簿中键入的值，我需要单击包含内部文本“协调”的 href。 HTML 表格包含许多行，我已将下面的 HTML 代码截断为仅包含 2 行以供说明(项目符号 2 和 3)。例如，使用“bullet 2”行，如果我的工作簿中的单元格等于“2205021”，我需要单击该行中的 href(“2205021”是 HTML 表格和我的 Excel 工作簿中唯一可识别的单元格值) .

请注意，我不想引用 href 的任何唯一可识别属性，例如“id”，因为我需要将代码编写为适用于我所有的 Excel 模板，仅依赖于唯一上面没有提到可识别的单元格值(除非有一种方法可以实现我想要的结果)。

如有任何帮助，我们将不胜感激

授予

到目前为止我的 VBA 代码:

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 "https://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")

mySelection = ThisWorkbook.Sheets("1").Range("b2").Value    
Select Case mySelection
Case "Preparer", "Reviewer", "Approver"
Case Else
    MsgBox "Invalid Selection!"
    ie.Quit
    Exit Sub
End Select

mySelObj.Value = mySelection

ieBusy ie

Set mySelObj = ie.Document.getElementById("ctl00_MainContent_ucDashboardPreparer_ucDashboardSettings_ctl00_ddlEffectiveDate")

mySelection = ThisWorkbook.Sheets("1").Range("c8").Value    
Select Case mySelection
Case "12/31/2017" 'I'll ref a cell in my workbook here once code is built out
Case Else
    MsgBox "Invalid Selection!"
    ie.Quit
    Exit Sub
End Select

---

相关 HTML 代码:

1."<table class=""rgMasterTable"" 
id=""ctl00_MainContent_assignedAccountsGrid_ctl00"" style=""border-width: 
0px; width: 100%; table-layout: auto; empty-cells: show;"" border=""0"" 
cellspacing=""1"" cellpadding=""2"">"

  <tbody>
2. <tr class="rgRow" id="ctl00_MainContent_assignedAccountsGrid_ctl00__0" style="white-space: nowrap;" onclick="javascript:needToConfirm = false;">
    <td style="display: none;">29937</td><td style="display: none;">0</td><td style="display: none;">3199</td><td style="display: none;">&nbsp;</td><td style="display: none;">False</td><td style="display: none;">False</td><td>
                            <img title="Not Started" id="ctl00_MainContent_assignedAccountsGrid_ctl00_ctl04_StatusIcon" style="border-width: 0px;" alt="Not Started" src="../images/document_plain_new.gif">
                        </td><td>
                            <a tabindex="33" id="ctl00_MainContent_assignedAccountsGrid_ctl00_ctl04_lnkReconcile" onclick="javascript:return CheckInterval();" href="javascript:__doPostBack('ctl00$MainContent$assignedAccountsGrid$ctl00$ctl04$lnkReconcile','')">Reconcile</a>
                        </td><td>US</td><td>CORP</td><td>DOM</td><td>2205021</td><td>XX</td><td>XX</td><td>XX</td><td>0L</td><td>Group Insurance Withholding</td><td align="center">12/31/2017</td><td align="center" class="Delinquent">1/28/2018</td><td align="right">10,920.72</td><td align="right">0.00</td><td>

                        </td><td align="right">&nbsp;</td><td align="right">10,920.72</td><td>Grant Bangerter</td><td>Ryan Smith</td><td>
                            <span id="ctl00_MainContent_assignedAccountsGrid_ctl00_ctl04_lblResponsibilty">Primary</span>
                        </td><td style="display: none;">False</td>
3. </tr><tr class="rgAltRow" id="ctl00_MainContent_assignedAccountsGrid_ctl00__1" style="white-space: nowrap;" onclick="javascript:needToConfirm = false;">
    <td style="display: none;">28148</td><td style="display: none;">0</td><td style="display: none;">3199</td><td style="display: none;">&nbsp;</td><td style="display: none;">False</td><td style="display: none;">False</td><td>
                            <img title="Not Started" id="ctl00_MainContent_assignedAccountsGrid_ctl00_ctl06_StatusIcon" style="border-width: 0px;" alt="Not Started" src="../images/document_plain_new.gif">
                        </td><td>
                            <a tabindex="33" id="ctl00_MainContent_assignedAccountsGrid_ctl00_ctl06_lnkReconcile" onclick="javascript:return CheckInterval();" href="javascript:__doPostBack('ctl00$MainContent$assignedAccountsGrid$ctl00$ctl06$lnkReconcile','')">Reconcile</a>
                        </td><td>US</td><td>CORP</td><td>DOM</td><td>GRP.2203015</td><td>XX</td><td>XX</td><td>XX</td><td>0L</td><td>Accrued Dental Insurance Expense</td><td align="center">12/31/2017</td><td align="center" class="Delinquent">1/28/2018</td><td align="right">-12,751,041.02</td><td align="right">0.00</td><td>

                        </td><td align="right">&nbsp;</td><td align="right">-12,751,041.02</td><td>Grant Bangerter</td><td>Ryan Smith</td><td>
                            <span id="ctl00_MainContent_assignedAccountsGrid_ctl00_ctl06_lblResponsibilty">Primary</span>
                        </td><td style="display: none;">False</td>

如果需要，我可以从网络表单中提供更多 HTML 代码。

Answer 1

您帖子中的 HTML 没有任何等于 2205021 的单元格。虽然有一个等于 GRP.2203015。

要从带有“GRP.2203015”的行中查找并单击 Reconcile 链接:

Dim elmID As Object, elmLink As Object

' get the cell in column 12 with text "GRP.2203015" '
Set elmID = FindElement(ie.document, "table tr td:nth-child(12)", text:="GRP.2203015")

' get the link in the parent row with text "Reconcile"
Set elmLink = FindElement(elmID.parentElement, "td a", text:="Reconcile")

' click the link '
elmLink.Click

对于 FindElement 函数:

Public Function FindElement(context As Object, selector As String, Optional text) As Object

    For Each FindElement In context.querySelectorAll(selector)
        If IsMissing(text) Or FindElement.innerText Like text Then Exit Function
    Next

    Err.Raise 9, , "Element not found for selector:" & selector & " text:" & text
End Function

请注意，您还可以搜索 text="*2203015" 以忽略前缀。