Automation macro using getelementsbyTagName opens first element but not 2nd

geophysguy

New Member
Joined
Dec 23, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code:
[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:

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>
 
Last edited by a moderator:

geophysguy

New Member
Joined
Dec 23, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
'    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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
I did not understand what kind of array syntax you are looking for; anyway, you will probably enjoy this page:

 

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top