none
Automate html form completion in VBA problems RRS feed

  • Question

  • I am trying to automate the completion and submission of a series of html forms in VBA. I am able to open the login form, automate the completion of the userID and Password fields as well as check a "disclosure" type checkbox, and clicking the submit button, which brings me to form 2. form 2 has no data to complete, All I need to do is click a button on form 2. I can't get that button to fire for anything. Consistent error 424 or error 91 and I have commented those problems in the code below.

    So I am new to this, and really don't know what I am doing, but I am thinking its because I am not actually on form 2 when I reach the code trying to click . Though when the code reaches ieapp.visable = true I do get form1, then form 2 visable, I feel like ieapp may still be pointing to form 1, because when I add iapp.refresh, I am back to form 1, with no info in the userid and password fields of form 1. It seems to me that form 1 has been refreshed. 

    Here's the form 2 button I want to click: <input name="enterERE" class="uef-btn" id="enterere" type="submit" value="Enter ERE">


    Here's my VBA code :

    Private Sub ERE_Reports_Click()
    Dim ieApp As InternetExplorer
    Dim iePage As HTMLDocument
    Dim Btn As HTMLFormElement

    Set ieApp = New InternetExplorer
    ieApp.Navigate "https://secure.ssa.gov/acu/LoginWeb/loginHandler.d..."

    'wait for page to load
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE
    Loop

    iePage.Forms(0).Item("userid").Value = "UserID"
    iePage.Forms(0).Item("password").Value = "Password"
    iePage.Forms(0).Item("accept").Click
    iePage.Forms(0).submit

    Do Until ieApp.ReadyState = READYSTATE_COMPLETE
    Loop
    ieApp.Visible = True

    'this takes me back to form 1 with no info in the userid and password fields, the same result as being on form1 and refreshing
    ieApp.Refresh2

    'render error 13
    Set Btn = iePage.getElementsByName("enterERE")
    Btn.Click
    'renders error 91
    iePage.getElementsByName("EnterERE").Item.Click
    'renders error 91
    iePage.getElementById("enterere").Click
    'iePage.getElementsByClassName("uef-btn").Item.Click
    'renders error 424
    iePage.Forms(0).Item("enter ere").Click
    'render error 438
    iePage.getElementsByTagName("enterERE").Click


    End Sub

    Tuesday, May 31, 2016 5:21 PM

All replies

  • I've had a lot of problems with IE 11.  I would recommend using Selenium Basic rather than Microsoft Internet Controls.  It comes with a lot of Excel examples.  It supports selecting elements with Xpath which is extremely powerful.  It supports Firefox, Chrome, IE and Edge.  I just finished a project with it and was really impressed.  It seems to be well supported with new updates every few months.  The latest release is 2.0.9.0.

    Selenium is used to test websites and is used by almost everyone.  You can use it with C#, Java, Ruby and other languages.  Someone developed Selenium Basic which supports VB.NET and VBA.



    • Edited by mogulman52 Tuesday, May 31, 2016 6:32 PM
    Tuesday, May 31, 2016 6:28 PM
  • Just for fun I used Selenium to login to my SS account.  I used Firefox.  I use Firefox's Firebug and Firepath to get all the element names. I put what I think are the Xpaths for your form in comments.

    Dim drv As Selenium.FirefoxDriver
    Dim text As String
    Dim ele As Selenium.WebElement
    
    
    Sub Login()
       '' Add reference to selenium type library
      Set drv = New Selenium.FirefoxDriver
      Dim xPath As String
      Dim browser As String
    
      drv.Get "https://secure.ssa.gov/RIL/SiView.do"
      xPath = "//input[@id='username']"  ''//input[@name='userId']
      If GetElement(xPath) Then
        ele.SendKeys "myUserId"
        xPath = "//input[@id='password']"  ''//input[@name='password']
        If GetElement(xPath) Then
          ele.SendKeys "myPassword"
          xPath = "//input[@id='signin']"  //input[@type='submit']
          If GetElementClick(xPath) Then
            drv.Wait (3000)
            drv.Window.SetSize 1200, 1000
            drv.Window.SetPosition 0, 0
            browser = drv.Manage.Capabilities.Get("browserName", 0)
          End If
        End If
      End If
    End Sub
    
    Function GetElement(xPath As String) As Boolean
      On Error GoTo Handler
      
      text = ""
      Set ele = drv.FindElementByXPath(xPath)
      text = ele.text
      GetElement = True
      Exit Function
    Handler:
      Err.Clear
      GetElement = False
    End Function
    
    Function GetElementClick(xPath As String) As Boolean
      On Error GoTo Handler
      
      text = ""
      Set ele = drv.FindElementByXPath(xPath)
      ele.Click
      drv.Wait (1000)
      GetElementClick = True
      Exit Function
    Handler:
      Err.Clear
      GetElementClick = False
    End Function




    • Edited by mogulman52 Tuesday, May 31, 2016 7:54 PM
    Tuesday, May 31, 2016 7:25 PM
  • Hmm, OK.  I thought Selenium was a program for testing webforms in development.
    Tuesday, May 31, 2016 8:33 PM
  • You can use it to control/scrape any website.  It is generally used for web development/testing but you can use whenever you want.  I use it to monitor and control a website.
    Tuesday, May 31, 2016 9:24 PM