Microsoft Excel - Clicking on Links Using VBA - Asked By Rajender Prasad on 12-Jul-13 12:25 PM

Dear All, I have the below html source, and I want to click on link based on 01/01/2026 - 03/31/2013 date. These are table values which are dynamic, here these are 3 might be any number of records. Please help

<td>
1</td>
<td>
A</td>
<td>
01/01/2026 - 03/31/2013</td>
<td>
[<a class='linkonLite' href="javascript:addsip('1311381');">add</a>]</td>
<td>
000442</td>
<td>
002</td>
<td>
07/01/2026 - 12/31/2010</td>
<td>
[<a class='linkonLite' href="javascript:maintainsip('885494');">update</a>][<a class='linkonLite' href="javascript:terminatesip('885494');">delete</a>]</td>
</tr>

<tr class='tiny' >
<td>
</td>
<td>
</td>
<td>
- </td>
<td>
</td>
<td>
004247</td>
<td>
002</td>
<td>
01/01/2026 - 12/31/2011</td>
<td>
[<a class='linkonLite' href="javascript:maintainsip('3886240');">update</a>][<a class='linkonLite' href="javascript:terminatesip('3886240');">delete</a>]</td>
</tr>

<tr class='tiny' >
<td>
</td>
<td>
</td>
<td>
- </td>
<td>
</td>
<td>
004247</td>
<td>
005</td>
<td>
01/01/2026 - 03/31/2013</td>
<td>
[<a class='linkonLite' href="javascript:maintainsip('6047933');">update</a>][<a class='linkonLite' href="javascript:terminatesip('6047933');">delete</a>]</td>
</tr>

Robbe Morris replied to Rajender Prasad on 12-Jul-13 12:31 PM

You are out of luck. These links are all tied into the JavaScript engine which doesn't "just run" in the html text. A web browser has to have the html and javascript loaded up into memory and execute it.

Your best bet is to attempt to use the ActiveX control WebBrowser on a VBA form and load your html into it and go from there.

Rajender Prasad replied to Robbe Morris on 12-Jul-13 12:38 PM

I tried something like below but every time its clicking on first link which not considering my date

For i6 = 0 To Iegps.document.all.tags("TD").Length - 1

s6 = Trim(Iegps.document.all.tags("TD").Item(i6).innerText)

      If s6 = "EMPLOYER SUBSIDY SUMMARY" Then

      'a = Iegps.document.all.tags("TD").Item(i6 - 3).innerText

      'subsidy_date = Trim(Right(Iegps.document.all.tags("TD").Item(i6 - 3).innerText, 10))


      If subsidy_date = DetachDate Or subsidy_date = "" Then


      For i8 = 0 To Iegps.document.all.tags("TD").Length - 1

        S8 = Trim(Iegps.document.all.tags("TD").Item(i8).innerText)

        If S8 = 01/01/2013 - 03/31/2013 Then


       Set colTr = Iegps.document.forms("resultsForm2").getElementsByTagName("TR")

For Each tr In colTr

    Set colTD = tr.getElementsByTagName("TD")

        For Each td In colTD

            If (td.innerText = a) Then


                For Each xWorkspace2 In tr.getElementsByTagName("a")


                    Iegps.navigate xWorkspace2.href

                    checkclick = checkclick + 1

                       If xWorkspace2.className = "linkonLite" And Trim(Mid(xWorkspace2, 12, 8)) = "maintain" Then


                            xWorkspace2.Click

                              checkclick = checkclick + 1



                        End If

               Next xWorkspace2
            End If
        Next td
Next tr

       End If

     Next i8

       End If

End If

Next i6

Robbe Morris replied to Rajender Prasad on 12-Jul-13 01:55 PM

After you do a .Navigate with the webbrowser control, you have to wait for the DocumentComplete event to fire before you can start working with the DOM (Document Object Model) of the page. So, you'll need to wire up the event handler for it. I'm not exactly sure how that is done inside of VBA. I don't do much work in that environment.

Rajender Prasad replied to Robbe Morris on 12-Jul-13 02:26 PM

i Got it resolved like below, may be its not the proper way to handle it.. but somehow I got it..

For i6 = 0 To Iegps.document.all.tags("TD").Length - 1

s6 = Trim(Iegps.document.all.tags("TD").Item(i6).innerText)

      If s6 = "APPLICATIONS SUMMARY" Then

      a = Iegps.document.all.tags("TD").Item(i6 - 3).innerText

      subsidy_date = Trim(Right(Iegps.document.all.tags("TD").Item(i6 - 3).innerText, 10))


      If subsidy_date = DetachDate Or subsidy_date = "" Then

          GoTo ClickOnSubsidyssumPanel


       End If

End If

Next i6

ClickOnSubsidyssumPanel:

For i10 = 0 To Iegps.document.all.tags("TD").Length - 1

s10 = Trim(Iegps.document.all.tags("TD").Item(i10).innerHTML)

If s10 = a Then

        DOD = Iegps.document.all.tags("TD").Item(i10 + 1).innerHTML

        Debug.Print DOD

        d = Mid(DOD, 58, 7)

       Call Iegps.document.parentWindow.execScript("javascript:maintainsip('" & d & "');")

        Subsidy = Subsidy + 1

End If

Next i10