Using Excel to submit information to a website form and parse the results

Question

Good afternoon,

I'm trying to set a means of automating some reporting for a in-house website. I teach at a college, and need to pull some data from faculty-accessible databases through the college-provided interface. So, I'm trying to access material I am authorized to see, will need to sign in to see, but don't want to repeat the steps potentially hundreds of times.

My thought is to:

1) log into the college-provided interface using IE with my credentials (so the system knows I'm authorized).

2) Provide excel with a list of ID numbers on the students I'm trying to track

3) use VBA to iterate through each ID, performing the following steps:

• Enter the ID number from the list in Excel into the appropriate form text box
• Click the "Submit" button
• Click a secondary "Submit" button on the resulting page
• Copy the resulting HTML text page that appears and parse it out for the information I need
• Move on to the next number and repeat

I've tried some of the other options I've seen (such as VBA to Enter Data Online and Submit Form) but am getting errors on the syntax.

The relevant portion of the HTML coding on the site I'm trying to reference is:

<FORM ACTION="action" METHOD="POST" NAME="idinputform">
Enter Number Here: 
<INPUT TYPE="number" NAME="ID_NUM" SIZE="9" MAXLENGTH="9">
<INPUT TYPE="hidden" NAME="refresh_proc" VALUE="menu_1">
<INPUT TYPE="submit" VALUE="Submit"> <INPUT TYPE="reset" VALUE="Reset">
</FORM>

What I've got in Excel is:

Sub QueryInfo()
    Application.ScreenUpdating = False
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "website address"
    IE.Document.idinputform.number.Value = "000000000" 
    Application.ScreenUpdating = True
End Sub

So, I start by opening an IE window and logging into the interface, and then start the VBA. The application runs, opens a new IE window, hits the website (which retains my login authority from the first one), and then crashes with an unspecified error.

It seems like this should be straightforward, but I'm just not seeing it.

Thanks! -G-

Answer 1

When dealing with collections (multiple objects) I find it is best to loop through the available objects, testing each as you go.

Sub QueryInfo()
    Dim ie As Object, iFRM As Long, iNPT As Long
    'Application.ScreenUpdating = False   'uncomment this once it is working
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.Navigate "website address"

    'wait untli the page loads
    Do While ie.busy Or ie.readyState <> 4  'READYSTATE_COMPLETE = 4
        DoEvents
    Loop

    With ie.document.body
        For iFRM = 0 To .getElementsByTagName("form").Length - 1
            If LCase(.getElementsByTagName("form")(iFRM).Name) = "idinputform" Then
                With .getElementsByTagName("form")(iFRM)
                    For iNPT = 0 To .getElementsByTagName("input").Length - 1
                        Select Case LCase(.getElementsByTagName("input")(iNPT).Name)
                            Case "id_num"
                                .getElementsByTagName("input")(iNPT).Value = 123
                            Case "refresh_proc"
                                .getElementsByTagName("input")(iNPT).Value = "menu_2"
                        End Select
                    Next iNPT
                    .submit    '<~~ submit the form
                    Do While ie.busy Or ie.readyState <> 4: DoEvents: Loop
                    Exit For
                End With
                Exit For
            End If
        Next iFRM
    End With

    With ie.document.body
        'should be at the form's destination
    End With

    Application.ScreenUpdating = True
End Sub

There could be multiple forms on the page. Go through each one until you find the one with the correct name. Inside that form definition, are multiple input elements; cycle through each and apply parameters as necessary. When that is complete, submit the form and exit the loop.

Answer 2

I have had times where I couldn't for the life of me get the Form Submission to work by using the HTML elements...I found a nice little work around by figuring out where the site sets it focus and then using keystrokes to make the inputs necessary.

I admit it isn't elegant, but it got me around what was an impasse that caused me many a late night.

Application.Wait (DateAdd("S", 1, Now()))
DoEvents
    Call SendKeys("{TAB}", True)
Application.Wait (DateAdd("S", 2, Now()))
Text2Clipboard (Format(Password1, "00000000"))
Application.Wait (DateAdd("S", 1, Now()))
DoEvents
    Call SendKeys("^v", True)
Application.Wait (DateAdd("S", 1, Now()))
DoEvents
    Call SendKeys("{TAB}", True)
Application.Wait (DateAdd("S", 2, Now()))
Text2Clipboard (Password2)
Application.Wait (DateAdd("S", 1, Now()))
DoEvents
    Call SendKeys("^v", True)
Application.Wait (DateAdd("S", 1, Now()))
DoEvents
    Call SendKeys("~", True)
Application.Wait (DateAdd("S", 2, Now()))
Dim Counter As Integer
Counter = 0
Do While IE.Busy
    Application.Wait (DateAdd("S", 5, Now()))
Loop
Application.Wait (DateAdd("S", 2, Now()))
Dim links, link As Object
Set links = IE.Document.getElementById("t-mainmenu").getElementsByTagName("a")
links(1).Click
Do While IE.Busy
    Application.Wait (DateAdd("S", 5, Now()))
Loop