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:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
Welcome to the Board

VBA Code:
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Option Explicit
Public Sub AOGCQueryHTMLDocument2() '''
Dim ie     As New SHDocVw.InternetExplorer, 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, HTMLWlBtns As MSHTML.IHTMLElementCollection
Dim btnShow As Long, IntWlCtr As Integer, WlBtnCompare As String, i%
ie.Visible = True
ie.navigate "http://www.aogc.state.ar.us/data/querybuild.aspx"
Do 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")
HTMLstr1.Click
'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
Set HTMLWlBtns = HTMLDoc.getElementsByTagName("input")
IntWlCtr = 0
'Comparison of "input" tags to determine button to click
For i = 4 To HTMLWlBtns.Length - 1
'    MsgBox HTMLWlBtns.Length & vbLf & HTMLWlBtns.item(4).id & vbLf & HTMLWlBtns.item(5).id, , i
    'Comparison of "input" tags to determine button to click
    Set HTMLWlBtn = HTMLWlBtns.item(i)
    WlBtnCompare = "cpMainContent_ChildContent2_Repeater1_btnShow_" & IntWlCtr
 '   MsgBox WlBtnCompare & vbLf & HTMLWlBtn.id & vbLf & HTMLWlBtns.item(i).id _
    & vbLf & HTMLWlBtns.item(4).id & vbLf & HTMLWlBtns.item(5).id, , i
    'This Debug.Print the 1st time through the loop opens "btnShow_0"
    If HTMLWlBtn.id = WlBtnCompare Then
        HTMLWlBtn.Click
        MsgBox "clicked", , IntWlCtr
        Sleep 6500
        IntWlCtr = IntWlCtr + 1
        Set HTMLDoc = ie.Document                               ' define it again
        Set HTMLWlBtns = HTMLDoc.getElementsByTagName("input")
    End If
Next
MsgBox "Well opening is complete", vbSystemModal
End Sub
 
Solution

geophysguy

New Member
Joined
Dec 23, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Thanks.
Any suggestions on what I’m doing wrong on the above?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
Is my code working for you?

The main thing I changed was to redefine the button collection as it was getting lost.

VBA Code:
Set HTMLDoc = ie.Document                               ' define it again
 

geophysguy

New Member
Joined
Dec 23, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks Worf worked like a charm on a selection with 20 of the button clicks, thought that might be what the problem was but wasn't placing the
VBA Code:
Set HTMLDoc = ie.Document
in the correct location.
Would you explain why it was getting lost and why the TagName "input" item number changes when I reset the HTMLDoc.
When I inspected the HTML in DevTools the QuerySelectorAll indicated that the Tag I was looking for was the 4th and 5th "input" but when I Debug.Printed it was the 4th and 13th Tag that opened.
While I'm at it any suggestions on using something other than the Sleep to wait on the AJAX response?
Once again I really appreciate the help as I've struggled with this for a couple of months and haven't been able to get a response.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
When you open a well the page reloads and the DOM is rebuilt. The code below writes the button collection to the sheet at two different moments, note that it changes.

Concerning the sleep method, it is not that elegant but if it is working, I would stick with it.

autom(AutoRecovered)nov20.xlsm
AB
1cpMainContent_ChildContent2_btnPlus__EVENTARGUMENT
2cpMainContent_ChildContent2_btnSearch__VIEWSTATE
3cpMainContent_ChildContent2_btnReset__VIEWSTATEGENERATOR
4cpMainContent_ChildContent2_Repeater1_btnShow_0__SCROLLPOSITIONX
5cpMainContent_ChildContent2_Repeater1_btnShow_1__SCROLLPOSITIONY
6cpMainContent_ChildContent2_btnYes__VIEWSTATEENCRYPTED
7cpMainContent_ChildContent2_btnOK__PREVIOUSPAGE
8cpMainContent_ChildContent2_btnClosescreenwidth
9cpMainContent_ChildContent2_btnErrorcpMainContent_ChildContent2_btnPlus
10cpMainContent_ChildContent2_btnErrOkcpMainContent_ChildContent2_btnSearch
11cpMainContent_ImageButton1cpMainContent_ChildContent2_btnReset
12__EVENTTARGETcpMainContent_ChildContent2_Repeater1_btnHide_0
13__EVENTARGUMENTcpMainContent_ChildContent2_Repeater1_btnHide_1
14__VIEWSTATEcpMainContent_ChildContent2_btnYes
15__VIEWSTATEGENERATORcpMainContent_ChildContent2_btnOK
16__SCROLLPOSITIONXcpMainContent_ChildContent2_btnClose
17__SCROLLPOSITIONYcpMainContent_ChildContent2_btnError
18__PREVIOUSPAGEcpMainContent_ChildContent2_btnErrOk
19__VIEWSTATEENCRYPTEDcpMainContent_ImageButton1
search


VBA Code:
Public Sub AOGCQueryHTMLDocument3()
Dim ie     As New SHDocVw.InternetExplorer, 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, btns As MSHTML.IHTMLElementCollection
Dim btnShow&, IntWlCtr%, WlBtnCompare As String, i%, j%
ie.Visible = True
ie.navigate "http://www.aogc.state.ar.us/data/querybuild.aspx"
Do 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 40000
Set HTMLstr1 = HTMLDoc.getElementById("cpMainContent_ChildContent2_GridLocation_ckLocation_1")
HTMLstr1.Click
'Hides the Location dropdown so can be searched
Set HTMLHide = HTMLDoc.getElementById("cpMainContent_ChildContent2_btnHideLocation")
HTMLHide.Click: Sleep 9000
'Searches for the Wells in the above Location
Set HTMLHSearch = HTMLDoc.getElementById("cpMainContent_ChildContent2_btnSearch")
HTMLHSearch.Click: Sleep 6000
Set HTMLDoc = ie.Document
Set btns = HTMLDoc.getElementsByTagName("input")
MsgBox btns.Length, 64, "Before loop"
For j = 1 To btns.Length - 1
    Cells(j, 1) = btns.item(j).id
Next
IntWlCtr = 0
For i = 4 To btns.Length - 1
    'Comparison of "input" tags to determine button to click
    Set HTMLWlBtn = btns.item(i)
    WlBtnCompare = "cpMainContent_ChildContent2_Repeater1_btnShow_" & IntWlCtr
    'This Debug.Print the 1st time through the loop opens "btnShow_0"
    If HTMLWlBtn.id = WlBtnCompare Then
        HTMLWlBtn.Click
        MsgBox "clicked", , IntWlCtr
        Sleep 6500
        IntWlCtr = IntWlCtr + 1
        Set HTMLDoc = ie.Document                               ' define it again
        Set btns = HTMLDoc.getElementsByTagName("input")
    End If
Next
MsgBox "Well opening is complete", vbSystemModal
For j = 1 To btns.Length - 1
    Cells(j, 2) = btns.item(j).id
Next
End Sub
 

geophysguy

New Member
Joined
Dec 23, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks Worf!!!
I see the changed tags in the html after I open the 1st well.
How did you know to look for the rewritten DOM? Because of the AJAX or just experience?
Is there any tutorials you'd recommend to get a better understanding of how to solve some of these types of questions with regard to AJAX and clicking on buttons?
I searched 100's of pages and saw nothing that told me the DOM would change that significantly. and to " 'define it again ".
Thanks again as you've done what this forum implied it was made for, which is to help novices gain a better understanding of programming VBA in a timely fashion.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
I noticed the web page blinked and it led me this way. I also used the VBE debugger to step through the code and confirm what was happening.

I heard an e-book will soon be available, dealing with this kind of stuff. I cannot start a forum conversation with you; I will try to remember to come back here and post about the book when I see it; if I forget, please post again on this thread.
 

geophysguy

New Member
Joined
Dec 23, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I'm continuing to work on my macro past the point where I am on this thread by reading in an array from an excel sheet that combines the id with the number from the array and I am getting a Object required (error 424) for the following:

VBA Code:
        Set HTMLstr1 = HTMLDoc.getElementById("cpMainContent_ChildContent2_GridLocation_ckLocation_" & "LocCntr")
the code works in the above macro with the following:
VBA Code:
Set HTMLstr1 = HTMLDoc.getElementById("cpMainContent_ChildContent2_GridLocation_ckLocation_1")
Any idea what's happening?

If I start another thread should I post all of the above again?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
If it relates to the original subject, I suppose we can continue here.

Try this:

VBA Code:
Set HTMLstr1 = HTMLDoc.getElementById("cpMainContent_ChildContent2_GridLocation_ckLocation_" & cstr(LocCntr))

Supposing that LocCntr = 1
 

Watch MrExcel Video

Forum statistics

Threads
1,130,022
Messages
5,639,596
Members
417,100
Latest member
Simon123456789

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