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