VBA to Submit Two Forms Sequentially IE Window

mbatta557

New Member
Joined
Jul 9, 2015
Messages
2
Hi There,

Been struggling w/ this code for a week or so now. Essentially, what I want to do is click on/fire the JavaScript link that shows up in form(2) (coach's Last Name, First Name) after pressing Submit on the button on form(0). However, VBA doesn't seem to be recognizing form(2) at all after I Submit the button. Is there something particular required to refocus after submitting the first form? Code is below, along w/ a list of all reference libraries I'm using.

References:
Visual Basic For Applications
Microsoft Excel 15.0 Object Library
OLE Automation
Microsoft Office 15.0 Object Library
Microsoft HTML Object Library

Code:
Public Sub Test()
Dim objWindow As Object
Dim objIEApp As Object
Dim objShell As Object
Dim objItem As Object
Dim l As Variant
Dim r As Long
Dim C As Long
Dim TDElements As Object
Dim TDElement As Object
Dim htmldoc As Object
Dim eleColtr As Object 'Element collection for tr tags
Dim eleColtd As Object 'Element collection for td tags
Dim eleRow As Object 'Row elements
Dim eleCol As Object 'Column elements
Dim ieURL As String 'URL
Dim i As Long
Dim j As Long
Dim CoachLast As String
Dim CoachFirst As String



CoachLast = "Jones"
CoachFirst = "James"

On Error GoTo Fin
Set objShell = CreateObject("Shell.Application")
Set objWindow = objShell.Windows()
For Each objItem In objWindow
If LCase(objItem.FullName Like "*iexplore*") Then
Set objIEApp = objItem
End If
Next objItem
If objIEApp Is Nothing Then
Set objIEApp = CreateObject("InternetExplorer.Application")
objIEApp.Visible = True
End If
With objIEApp
.Visible = True
.Navigate "http://web1.ncaa.org/stats/StatsSrv/careersearch"
While Not .ReadyState = 4
DoEvents
Wend
.document.all.lastName.Value = CoachLast
.document.all.firstName.Value = CoachFirst
.document.all.Item("playerCoach")(1).Checked = "True"
.document.forms(0).submit
.document.forms(2).submit


Set htmldoc = objIEApp.document 'Document webpage
Set eleColtr = htmldoc.getElementsbytagname("tr") 'Find all tr tags

'This section populates Excel
i = 0 'start with first value in tr collection
For Each eleRow In eleColtr 'for each element in the tr collection
Set eleColtd = htmldoc.getElementsbytagname("tr")(i).getElementsbytagname("td") 'get all the td elements in that specific tr
j = 0 'start with the first value in the td collection
For Each eleCol In eleColtd 'for each element in the td collection
Sheets("Sheet1").Range("A3").Offset(i, j).Value = eleCol.innertext 'paste the inner text of the td element, and offset at the same time
j = j + 1 'move to next element in td collection
Next eleCol 'rinse and repeat
i = i + 1 'move to next element in td collection
Next eleRow 'rinse and repeat

End With

Fin:
If Err.Number <> 0 Then MsgBox "Error: " & _
Err.Number & " " & Err.Description
Set objWindow = Nothing
Set objShell = Nothing
End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi mbatta,
I assume that the page is refreshed after ".document.forms(0).submit"? I guess that indeed you will have to "refocus" your IE object, as a new page was loaded after that first submit, it might be that that second page has only one form?
Ciao,
Koen
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top