Results 1 to 10 of 10

Thread: [RESOLVED] [Excel] VBA code to automate clicking Yes Button in IE

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2013
    Posts
    3

    Resolved [RESOLVED] [Excel] VBA code to automate clicking Yes Button in IE

    I need some assistance to click the Yes button on a web page

    Website: http://www.sheknows.com/beauty-and-s...giveaway/enter

    I can click the Submit button fine but on the second page I have not been able to click the Yes button

    HTML Code:
    <div class="verify-entry">
    	<div class="form-container">
    		<!--<div class="transparent-background"></div>-->
    		<h3>Is the following information correct?</h3>
    		<dl>
    			<dt>First Name:</dt> <dd> </dd>
    			<dt>Last Name:</dt> <dd> </dd>
    			<dt>Phone:</dt> <dd> </dd>
    			<dt>Address:</dt> <dd> </dd>
    			<dt>City:</dt> <dd> </dd>
    			<dt>State:</dt> <dd> </dd>
    			<dt>Zip:</dt> <dd> </dd>
    			<dt>Email:</dt> <dd> </dd>
    								</dl>
    <a href="/giveaway/250-to-pamper-your-loved-one-for-valentines-day-giveaway/terms" class="button button-yes">Yes</a>
    		
    <a href="/giveaway/250-to-pamper-your-loved-one-for-valentines-day-giveaway/enter" class="button-no">Cancel</a>
    	</div>
    </div>
    Here is the VBA code I have tried so far:

    Code:
    Set inputCollection = ie.document.getElementsByTagName("a")
    
    For Each aelement In inputCollection
    If aelement.getAttribute("class") = "button button-yes" Then
    aelement.Click

    Code:
    Set ElementCol = ie.document.getElementsByTagName("a")
    
    
            For Each ele In ie.document.getElementsByTagName("a")
                If InStr(ele.innerhtml, "terms") > 0 Then
                ele.Click

    Code:
    Set ElementCol = ie.document.getElementsByTagName("a")
    
    
            For Each ele In ie.document.getElementsByTagName("a")
                If InStr(ele.innertext, "Yes") > 0 Then
                ele.Click


    None of these have worked.

    Anyone have any other ideas?

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel] VBA code to automate clicking Yes Button in IE

    Code:
    For Each ele In wb.document.getelementsbytagname("a")
        If InStr(ele.innertext, "Yes") > 0 Then ele.Click
    Next
    works for me
    or
    if ele.innertext = "Yes" then ele.click
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2013
    Posts
    3

    Re: [Excel] VBA code to automate clicking Yes Button in IE

    Quote Originally Posted by westconn1 View Post
    Code:
    For Each ele In wb.document.getelementsbytagname("a")
        If InStr(ele.innertext, "Yes") > 0 Then ele.Click
    Next
    works for me
    or
    if ele.innertext = "Yes" then ele.click
    Thanks so much for your response. However, I was not able to get this to work. First I got an Object Required Error. I changed all references to ie in my code to wb and no longer got the error but neither did it click the button. Here is my VBA code in its entirety if that helps (I changed the references back to ie)

    Code:
    Set ie = CreateObject("InternetExplorer.Application")
    Dim Cell As Range
    Set Webcol = Range("A2:A2") 'Range containing hyper links
    Dim PauseTime, Start
    WaitHours = 0
    WaitMin = 20
    WaitSec = 0
    
       ActiveCell.Select
       
        If ActiveCell.Value <> "" Then
        ie.navigate ActiveCell.Hyperlinks(1).Address
        ie.Visible = True
     
        End If
      
        
      'Pause while page loads
      
        Do Until ie.readyState = 4
        DoEvents
        Loop
        
      'Pause while RoboForm populates data
        
        PauseTime = 5 ' Set duration in seconds
        Start = Timer ' Set start time.
        Do While Timer < Start + PauseTime
        DoEvents ' Yield to other processes.
        Loop
       
     'Click Submit button
    
        ie.document.forms(1).Submit
          
        
    'Pause while page loads
      
        Do Until ie.readyState = 4
        DoEvents
        Loop
        '
        
    'Pause while RoboForm populates data
        
        PauseTime = 5 ' Set duration in seconds
        Start = Timer ' Set start time.
        Do While Timer < Start + PauseTime
        DoEvents ' Yield to other processes.
        Loop
        
    
           
     
    ' loop through all 'input' elements and find the one with the value "Yes"
    
    For Each ele In ie.document.getelementsbytagname("a")
        If InStr(ele.innertext, "Yes") > 0 Then ele.Click
    
    
    Next
    
    
    
    
     'Pause while page loads
      
        Do Until ie.readyState = 4
        DoEvents
        Loop
        
     'Pause
        
        PauseTime = 5 ' Set duration in seconds
        Start = Timer ' Set start time.
        Do While Timer < Start + PauseTime
        DoEvents ' Yield to other processes.
        Loop
        
        
        
        
     ' loop through all 'input' elements and find the one with the value "Submit.Gif"
    
           For Each ele In ie.document.getElementsByTagName("a")
                If InStr(ele.innerhtml, "submit.gif") > 0 Then
                ele.Click
               
          Exit For
          
          End If
                
            
    
            Next
        
        'Pause
        
        PauseTime = 5 ' Set duration in seconds
        Start = Timer ' Set start time.
        Do While Timer < Start + PauseTime
        DoEvents ' Yield to other processes.
        Loop
        
       
             
    ie.Quit
     
    End Sub
    Again, it didn't work regardless of whether I used wb or ie. I am pretty new to VBA so perhaps I am missing something obvious?

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel] VBA code to automate clicking Yes Button in IE

    try a longer delay for the page to finish populating, make the ie visible, then just put a breakpoint wait until you can see the page has finished before continuing, if it works then, figure how much more delay to put in your code

    Again, it didn't work regardless of whether I used wb or ie.
    i always use wb, just a choice of variable name, normally i change before posting the code, but forgot this time, keep using the one you like
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2013
    Posts
    3

    Re: [Excel] VBA code to automate clicking Yes Button in IE

    Thank you so much!

    That worked.

    I bumped the wait time up from 5 to 6 seconds and that did the trick

  6. #6
    New Member
    Join Date
    Jul 2017
    Posts
    3

    Re: [RESOLVED] [Excel] VBA code to automate clicking Yes Button in IE

    Hello

    I am having a similar problem. I am trying to click a button but it seems not to have a name associated with it. The only possible solution is to hit Enter. I have tried using the above mentioned code but it doesn't' seems to work. The input in the two fields are coming directly from my excel file but the Calculate button is not working. Any help would be greatly appreciated.

    Code:
    Private Sub Workbook_Open()
    
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate "http://www.earthquakescanada.nrcan.gc.ca/hazard-alea/interpolat/index_2010-en.php"
    IE.Visible = True
    
    
    Do
    DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE
    
    IE.document.all("in_lat").Value = ThisWorkbook.Sheets("sheet1").Range("b1")
    IE.document.all("in_lon").Value = ThisWorkbook.Sheets("sheet1").Range("c1")
    
    
    
    For Each ele In IE.document.getElementsByTagName("details")
        If ele.innerText = "Calculate" Then ele.Click
    Next
    
    End Sub

  7. #7
    New Member
    Join Date
    Jul 2017
    Posts
    3

    Re: [Excel] VBA code to automate clicking Yes Button in IE

    Hi Westconn1, would you mind looking at my issue listed above.

    Thanks.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] [Excel] VBA code to automate clicking Yes Button in IE

    you can try like
    Code:
    For Each ele In IE.Document.getElementsByTagName("input")
        ''Debug.Print ele.Value, ele.outerhtml
        If ele.Value = "Calculate" Then ele.Click
    Next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    New Member
    Join Date
    Jul 2017
    Posts
    3

    Re: [RESOLVED] [Excel] VBA code to automate clicking Yes Button in IE

    Quote Originally Posted by westconn1 View Post
    you can try like
    Code:
    For Each ele In IE.Document.getElementsByTagName("input")
        ''Debug.Print ele.Value, ele.outerhtml
        If ele.Value = "Calculate" Then ele.Click
    Next
    Thank You westconn1. It worked just great. Last question.

    I have been trying to fetch the calculated values from the first table directly into the excel. The table has unfortunately no names associated with it and I have been testing the a few things which for some reason doesn't seems to work. Following are the table values that I am trying to fetch into excel after computation. I only want the values underneeth the Sa(0.2) and others. Its the first table only. Would you mind seeing what might be the issue here. Thanks!

    Name:  Capture.jpg
Views: 12675
Size:  14.8 KB

    Code:
    Sub NBCC2015()
    
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate "http://www.earthquakescanada.nrcan.gc.ca/hazard-alea/interpolat/index_2010-en.php"
    IE.Visible = True
    
    'Loading the Internet Explorer
    While IE.busy
    DoEvents
    Wend
    
    'Example values for the coordinates
    IE.document.all("in_lat").Value = 43.4049811
    IE.document.all("in_lon").Value = -80.5197659
    
    'Original code lines linked with excel file
    'IE.document.all("in_lat").Value = ThisWorkbook.Sheets("sheet1").Range("b1")
    'IE.document.all("in_lon").Value = ThisWorkbook.Sheets("sheet1").Range("c1")
    
    'Calculating the results by pressing the Calculate button on webpage
    For Each ele In IE.document.getElementsByTagName("input")
        ''Debug.Print ele.Value, ele.outerhtml
        If ele.Value = "Calculate" Then ele.Click
    Next
    
    
    '' ***HERE THE ERROR STARTS*** ''
    
    'Fetching the first table values to Excel file
    Dim Doc As HTMLDocument
    Set Doc = IE.document
    
    Dim sTR As String
    sTR = Trim(Doc.getElementsByTagName("tr")(1).innerText)
    
    'Testing if code works
    MsgBox sdd
    
    'Writing the fetched file to A10 cell for testing
    Sheets("Sheet1").Range("A10").Value = IE.document.getelementbyID("_2p_sa02").Value
    
    
    End Sub

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] [Excel] VBA code to automate clicking Yes Button in IE

    i tested like
    Code:
    'Fetching the first table values to Excel file
    'Dim Doc As HTMLDocument
    Do: Loop Until IE.ReadyState = 4 Or Not IE.Busy
    '** you need a delay here, you will have to experiment how long, change the 6 seconds to whatever value suits
    t = Timer
    Do: Loop Until Timer - t > 6
    'Set Doc = IE.Document
    
    Dim sTR As String
    sTR = Trim(IE.Document.getElementsByTagName("td")(0).innerText)
    
    'Testing if code works
    Debug.Print sTR
    i also had to change the initial load of the page like
    Code:
    Do: Loop Until IE.ReadyState = 4 Or Not IE.Busy
    as sometimes it errored before putting in the values, when just looping while busy, you may also need a similar delay as above if error still occurs
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width