Automation macro using getelementsbyTagName opens first element but not 2nd

[geophysguy]

I need help trying to automate the opening of tables by clicking on a MSHTML.IHTMLElement TagName if it matches the tag.id I'm looking for. The tag.id is shown in the immediate window as equaling the one I'm searching for and it opens the 1st table. The 2nd tag.id is equal to the desired ID that calls for the table to be opened but the object gets set to nothing and the 2nd table doesn't open. In the watch window the object gives a Object variable or With block variable not set error. The website uses AJAX to load the data after clicks and any suggestions to use something instead of sleep would be greatly appreciated.

Need help unraveling why 2nd table won't open and Object variable gets set to nothing after 1st loop.

I've done a little VBA and I've researched this problem extensively and can't find an answer.
Thanks in advance.

Here's the code I have:

[CODE=vba]
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Option Explicit
Public Sub AOGCQueryHTMLDocument2()'''

Dim ie     As New SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.IHTMLDocument
Dim HTMLLoc As MSHTML.IHTMLElement, HTMLstr1 As MSHTML.IHTMLElement, HTMLWlBtn As MSHTML.IHTMLElement
Dim HTMLHide As MSHTML.IHTMLElement, HTMLstrWait As MSHTML.IHTMLElement
Dim HTMLHSearch As MSHTML.IHTMLElement
Dim HTMLWlBtns As MSHTML.IHTMLElementCollection
Dim btnShow As Long, IntWlCtr As Integer
Dim WlBtnCompare As String, i As Integer

ie.Visible = True
ie.navigate "http://www.aogc.state.ar.us/data/querybuild.aspx"
o While ie.readyState <> READYSTATE_COMPLETE
Loop

Set HTMLDoc = ie.document

'Sets and Clicks to open the location from which wells will be searched
Set HTMLLoc = HTMLDoc.getElementById("cpMainContent_ChildContent2_btnViewLocation")
HTMLLoc.Click

Sleep 6000

'Clicks on the Location
Set HTMLstr1 = HTMLDoc.getElementById("cpMainContent_ChildContent2_GridLocation_ckLocation_1")
With HTMLstr1
    .Click
End With

'Hides the Location dropdown so can be searched
Set HTMLHide = HTMLDoc.getElementById("cpMainContent_ChildContent2_btnHideLocation")
HTMLHide.Click

Sleep 6000

'Searches for the Wells in the above Location
Set HTMLHSearch = HTMLDoc.getElementById("cpMainContent_ChildContent2_btnSearch")
HTMLHSearch.Click

Sleep 6000

Set HTMLDoc = ie.document

'Gets all "input" TagNames
Set HTMLWlBtns = HTMLDoc.getElementsByTagName("input")

IntWlCtr = 0

'Comparison of "input" tags to determine button to click
   For i = 4 To HTMLWlBtns.Length - 1

        With HTMLWlBtns
            On Error Resume Next
                Debug.Print HTMLWlBtns.Length, HTMLWlBtns.Item(4).ID, HTMLWlBtns.Item(5).ID
                 'Debug.Print shows length and nodes correctly
                 'HTMLWlBtns.Length = 20          
                 'HTMLWlBtns.Item(4).ID = cpMainContent_ChildContent2_Repeater1_btnShow_0        
                 'HTMLWlBtns.Item(5).ID = cpMainContent_ChildContent2_Repeater1_btnShow_1
        End With

   'Comparison of "input" tags to determine button to click
        With HTMLWlBtn
            Set HTMLWlBtn = HTMLWlBtns.Item(i)
        End With

        WlBtnCompare = "cpMainContent_ChildContent2_Repeater1_btnShow_" & IntWlCtr
            Debug.Print WlBtnCompare, HTMLWlBtn.ID, HTMLWlBtns.Item(i).ID, HTMLWlBtns.Item(4).ID, HTMLWlBtns.Item(5).ID

      'This Debug.Print the 1st time through the loop opens "btnShow_0"
      'The 2nd time through the HTMLWlBtn.ID is nothing
      'Need explanation as to why HTMLWlBtns.Item(5).ID gets set to nothing  

        If HTMLWlBtn.ID = WlBtnCompare Then
            HTMLWlBtn.Click

            Sleep 6500

            IntWlCtr = IntWlCtr + 1
        End If
    Next i

MsgBox "Well opening is complete", vbSystemModal

End Sub

Here is the HTML:

<div style="margin-left:15px; margin-right:5px; margin-bottom:5px;">
              <table cellpadding="0" cellspacing="0">
                  <tbody><tr>
                      <td>
                        
                          <input type="image" name="ctl00$ctl00$cpMainContent$ChildContent2$Repeater1$ctl00$btnShow" id="cpMainContent_ChildContent2_Repeater1_btnShow_0" src="../Images/plus.gif">
                        
                          <span style="color:Black; font-weight:bold ">API</span>&nbsp;&nbsp;-
                          <span id="cpMainContent_ChildContent2_Repeater1_lblAPIWellNo_0" style="font-weight:bold;">03-077-00004-00-00</span>
                          &nbsp;&nbsp;<span style="color:Black; font-weight:bold ">Permit Number</span>&nbsp;&nbsp;-
                          <span id="cpMainContent_ChildContent2_Repeater1_lblPermit_0" style="font-weight:bold;">18684</span>
                          &nbsp;&nbsp;<span style="color:Black; font-weight:bold ">Well Name</span>&nbsp;&nbsp;-                                           
                          <span id="cpMainContent_ChildContent2_Repeater1_lblWellName_0" style="font-weight:bold;">Bosnick Oper 1</span>
                      </td>
                      <td align="right">
                          &nbsp;&nbsp; | &nbsp;&nbsp;
                          <span style="color:Maroon; font-weight:bold ">SideTrack Cnt</span>&nbsp;&nbsp;-
                          <span id="cpMainContent_ChildContent2_Repeater1_lblRowTotal_0" style="font-size:10pt;font-weight:bold;">1</span>
                      </td>
                  </tr>
                
              </tbody></table>
        
              <table cellpadding="0" cellspacing="0">
                  <tbody><tr>
                      <td>
                        
                          <input type="image" name="ctl00$ctl00$cpMainContent$ChildContent2$Repeater1$ctl01$btnShow" id="cpMainContent_ChildContent2_Repeater1_btnShow_1" src="../Images/plus.gif">
                        
                          <span style="color:Black; font-weight:bold ">API</span>&nbsp;&nbsp;-
                          <span id="cpMainContent_ChildContent2_Repeater1_lblAPIWellNo_1" style="font-weight:bold;">03-077-10002-00-00</span>
                          &nbsp;&nbsp;<span style="color:Black; font-weight:bold ">Permit Number</span>&nbsp;&nbsp;-
                          <span id="cpMainContent_ChildContent2_Repeater1_lblPermit_1" style="font-weight:bold;">38665</span>
                          &nbsp;&nbsp;<span style="color:Black; font-weight:bold ">Well Name</span>&nbsp;&nbsp;-                                           
                          <span id="cpMainContent_ChildContent2_Repeater1_lblWellName_1" style="font-weight:bold;">Bosnick 1-1</span>
                      </td>
                      <td align="right">
                          &nbsp;&nbsp; | &nbsp;&nbsp;
                          <span style="color:Maroon; font-weight:bold ">SideTrack Cnt</span>&nbsp;&nbsp;-
                          <span id="cpMainContent_ChildContent2_Repeater1_lblRowTotal_1" style="font-size:10pt;font-weight:bold;">1</span>
                      </td>
                  </tr>
                
              </tbody></table>
        
      <br><br>
  </div>

 

[geophysguy]

Thanks Worf, I saw the cstr in another search I'll try that too.
I went a little farther and concatenated the element id in excel and read into an array and finally got it to work. Here's what I did:

'    Clicks on the STR of wells from array
    For xa = LBound(ArrSTR) To UBound(ArrSTR)
    Set HTMLstr1 = HTMLDoc.getElementById(ArrSTR(xa, 1))
    HTMLstr1.Click
    Next xa

It took me a while to get the exact syntax of the variable into the getElementByID 2n () defining row and column in the array.
Is there an array.value type syntax in VBA that would allow you to loop through?
Thanks again Worf.
Finally getting things figured out in days instead of months. Next is hours not days. Looking for minutes.

 

[Worf]

I did not understand what kind of array syntax you are looking for; anyway, you will probably enjoy this page:

VBA Arrays