Use Excel VBA to click on a button in Internet Explorer, when the button has no "name" associated

Question

I'm trying to use excel to automate the value entering in a time sheet. The time sheet is on a web page. Right now I'm able to load the page, enter my username and password and then entering the time sheet by itself. See code below.

At this point I need to click on a button to open sub-forms. I can't know in advance how many sub-forms there will be to open. I know how to click on a button when it has a "name". But in this case there's none. So my updated code below use a loop to open every other subform. It works the first time, but when I do it again

Could someone point me how to determine how many of those button there is in the page and how to click on each? Following I'm placing the code I have until now and below it, the HTML code of the page I need to interact with.

Private Sub time_sheet_filling()

    Dim I As Long
    Dim IE As Object
    Dim doc As Object
    Dim objElement As Object
    Dim objCollection As Object

    ' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True

    ' Send the form data To URL As POST binary request
    IE.navigate "http://timesheet.cccc.ca/timesheet/"

    ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop

        'Load the logon page
    Set objCollection = IE.Document.getElementsByTagName("input")
    I = 0
    While I < objCollection.Length
        If objCollection(I).Name = "txtUserName" Then
            ' Set text to enter
            objCollection(I).Value = "6666"
        End If
        If objCollection(I).Name = "txtPwd" Then
            ' Set text for password
            objCollection(I).Value = "password"
        End If
        If objCollection(I).Type = "submit" And objCollection(I).Name = "btnSubmit" Then ' submit button clicking
            Set objElement = objCollection(I)
        End If
        I = I + 1
    Wend

    objElement.Click    ' click button to load the form

    ' Wait while IE re-loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop

    ' Show IE
    IE.Visible = True  
Dim links, link
Dim n, j
Set links = IE.Document.getElementById("dgTime").getElementsByTagName("a")
n = links.Length  
For j = 0 To n - 1 Step 2
    links(j).Click
'I have some operations to be done will post another question for this
IE.Document.getElementById"DetailToolbar1_lnkBtnSave").Click              'save
IE.Document.getElementById"DetailToolbar1_lnkBtnCancel").Click            'close
Next



End Sub

So extract of the html code is below. I'm trying to click the button that is coded in the last line of the html code below

<table width="984" class="Grid" id="dgTime" border="1" rules="all" cellspacing="0">
  <tbody>
    <tr class="GridHeader">
    </tr>
    <tr class="GridItem">
    </tr>
    <tr class="GridItem">
      <td class="GridButtonColumn">
        <a href="javascript:__doPostBack('dgTime$_ctl2$_ctl0','')">
          <img src="images/toolbar/b_edit.gif">
        </a>
      </td

Tx Tim for the answers. Now I'm able to select the first subform button to open it.

links(j).click   'j = 0

I then save it, close, and come back to the main form. But then when I try to do

links(j).click   'j = 2 this time

the second time I get a runtime error 70: permission denied. Anymore kind help will be so appreciated. Regards

Well that is an anchor, not a button. So select it via tagName — epascarello, Jan 26 '15 at 16:05
I'm sorry but I don't understant your comment. How can I find the tagName in the HTML code above? Is it "dgTime$_ct12$_ct10" ? And if so how can I select this one and all the later (ct12$, ct13$ and so on) if I don't know in advance how much there will be? Tx. — excelguigui11, Jan 26 '15 at 16:28
Well that is not the tag name I am referring too.... https://developer.mozilla.org/en-US/docs/Web/API/Element.getElementsByTagName — epascarello, Jan 26 '15 at 16:33

score 6 · Accepted Answer · answered Jan 28 '15 at 19:36

With the kind help from Tim Williams, I finally figured out the last détails that were missing. Here's the final code below.

Private Sub Open_multiple_sub_pages_from_main_page()


Dim i As Long
Dim IE As Object
Dim Doc As Object
Dim objElement As Object
Dim objCollection As Object
Dim buttonCollection As Object
Dim valeur_heure As Object


' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.navigate "http://webpage.com/"

' Wait while IE loading...
While IE.Busy
        DoEvents
Wend


Set objCollection = IE.Document.getElementsByTagName("input")

i = 0
While i < objCollection.Length
    If objCollection(i).Name = "txtUserName" Then
        ' Set text for search
        objCollection(i).Value = "1234"
    End If
    If objCollection(i).Name = "txtPwd" Then
        ' Set text for search
        objCollection(i).Value = "password"
    End If

    If objCollection(i).Type = "submit" And objCollection(i).Name = "btnSubmit" Then ' submit button if found and set
        Set objElement = objCollection(i)
    End If
    i = i + 1
Wend
objElement.Click    ' click button to load page

' Wait while IE re-loading...
While IE.Busy
        DoEvents
Wend

' Show IE
IE.Visible = True
Set Doc = IE.Document

Dim links, link

Dim j As Integer                                                                    'variable to count items
j = 0
Set links = IE.Document.getElementById("dgTime").getElementsByTagName("a")
n = links.Length
While j <= n                                    'loop to go thru all "a" item so it loads next page
    links(j).Click
    While IE.Busy
        DoEvents
    Wend
    '-------------Do stuff here:  copy field value and paste in excel sheet.  Will post another question for this------------------------
    IE.Document.getElementById("DetailToolbar1_lnkBtnSave").Click              'save
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)                                   'wait
    Loop
    IE.Document.getElementById("DetailToolbar1_lnkBtnCancel").Click            'close
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)                                   'wait
    Loop
    Set links = IE.Document.getElementById("dgTime").getElementsByTagName("a")
    j = j + 2
Wend    
End Sub

Tim Williams · Answer 2 · 2015-01-27T23:09:16.857

3

IE.Document.getElementById("dgTime").getElementsByTagName("a")(0).Click

EDIT: to loop through the collection (items should appear in the same order as they are in the source document)

Dim links, link 

Set links = IE.Document.getElementById("dgTime").getElementsByTagName("a")

'For Each loop
For Each link in links
    link.Click
Next link

'For Next loop
Dim n, i
n = links.length
For i = 0 to n-1 Step 2
    links(i).click
Next I

edited Jan 27 '15 at 23:09

answered Jan 26 '15 at 22:11

Tim Williams

154,628
8
97
125

Tx Tim for the answer. This will have me to be able to click on the first element. So I was thinking on using the following to open all the items. Set buttonCollection = IE.Document.getelementbyid"dgTime").getElementsByTagName("a") '(0) j = 1 'buttonCollection.Length While j <= buttonCollection.Length – excelguigui11 Jan 27 '15 at 15:00
Tx again Tim, I guess I could have mention that I need to select every other element, first, third, fifth and so on. How can I skip one if I use the "for each" method? My last question wasn't precise enough and I apologize. Thank you for the generosity of your time and knowledge. – excelguigui11 Jan 27 '15 at 22:01
I'm keep looking to find my solution with the "link.click" method. So to be sure I don't bother again on the same matter, how could I select let's say the 9th item of the "link" in the "links" in the answer above – excelguigui11 Jan 27 '15 at 22:17

score 3 · Answer 3 · answered Jun 29 '18 at 19:17

CSS selector:

Use a CSS selector of img[src='images/toolbar/b_edit.gif']

This says select element(s) with img tag with attribute src having value of 'images/toolbar/b_edit.gif'

CSS query:

VBA:

You can apply the selector with the .querySelector method of document.

IE.document.querySelector("img[src='images/toolbar/b_edit.gif']").Click

Use Excel VBA to click on a button in Internet Explorer, when the button has no "name" associated

3 Answers3

Linked

Related