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/2013 - 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/2006 - 03/31/2013</td>
<td>
<nobr>[<a class='linkonLite' href="javascript:addsip('1311381');">add</a>]</nobr></td>
<td>
000442</td>
<td>
002</td>
<td>
07/01/2007 - 12/31/2010</td>
<td>
<nobr>[<a class='linkonLite' href="javascript:maintainsip('885494');">update</a>][<a class='linkonLite' href="javascript:terminatesip('885494');">delete</a>]</nobr></td>
</tr>
 
<tr  class='tiny'  >
<td>
&nbsp;</td>
<td>
&nbsp;</td>
<td>
&nbsp; - &nbsp;</td>
<td>
<nobr></nobr></td>
<td>
004247</td>
<td>
002</td>
<td>
01/01/2011 - 12/31/2011</td>
<td>
<nobr>[<a class='linkonLite' href="javascript:maintainsip('3886240');">update</a>][<a class='linkonLite' href="javascript:terminatesip('3886240');">delete</a>]</nobr></td>
</tr>
 
<tr  class='tiny'  >
<td>
&nbsp;</td>
<td>
&nbsp;</td>
<td>
&nbsp; - &nbsp;</td>
<td>
<nobr></nobr></td>
<td>
004247</td>
<td>
005</td>
<td>
01/01/2013 - 03/31/2013</td>
<td>
<nobr>[<a class='linkonLite' href="javascript:maintainsip('6047933');">update</a>][<a class='linkonLite' href="javascript:terminatesip('6047933');">delete</a>]</nobr></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