VBA Automation of Internet Explorer Child Window

Sub ParseReportFromWeb()
Set appIE = New InternetExplorer
sURL = "http://basesvr/app1/main.aspx#"

With appIE
    .Navigate sURL
    .Visible = True  'allows for viewing the web page
End With

' loop until the page finishes loading
Do While appIE.Busy: Loop

'go thru each link element and list info
Set ElementCol = appIE.Document.getElementsByTagName("a")

For Each Link In ElementCol
    'If Link.innerHTML = "<B>Clickable Text Link Name</B>" Then
    If InStr(1,Link.innerText, "Run Report") <> 0 Then
        Link.Click
        Exit For
    End If
Next Link

Do While appIE.Busy
    Loop
 
' the report window pops up and I need code to manipulate
End Sub

Option Explicit

Public WithEvents IE1 As InternetExplorer
Public IE2 As InternetExplorer


Private Sub ParseReportFromWeb()

Dim sURL As String
Dim ElementCol As IHTMLElementCollection
Dim link As HTMLAnchorElement

'Example web site which has links which open in a new window
sURL = "http://www.dh.gov.uk/en/index.htm"

Set IE1 = New InternetExplorer
Set IE2 = Nothing

With IE1
    .navigate sURL
    .Visible = True  'allows for viewing the web page
End With

' loop until the page finishes loading
Do While IE1.Busy: Loop

Set ElementCol = IE1.document.getElementsByTagName("a")

'Find and click the 'Videos (opens new window)' link
For Each link In ElementCol
    If InStr(link.innerText, "Videos") = 1 Then
        link.Click
        Exit For
    End If
Next link

'Ensure new window has been created
Do While IE2 Is Nothing: Loop

Do While IE2.Busy: Loop
 
'Click first link in the new window
Set ElementCol = IE2.document.getElementsByTagName("a")
Set link = ElementCol(0)
link.Click

End Sub

Private Sub IE1_NewWindow2(ppDisp As Object, Cancel As Boolean)
    Set IE2 = New InternetExplorer
    Set ppDisp = IE2.Application
    Debug.Print "NewWindow2"
End Sub

Public WithEvents IE1 As InternetExplorer

Option Explicit
Public WithEvents IE1 As InternetExplorer
Public IE2 As InternetExplorer
 
Private Sub CommandButton1_Click()
Dim strURL As String
Dim ElementCol As IHTMLElementCollection
Dim link As HTMLAnchorElement
Dim strUsername As String
Dim strPassword As String
Dim ieDoc As Object    ' the document retrieved ...
Dim IeTable As Object  ' the table where our data resides ...
Dim ieCell As Object   ' the cells (in the table above) holding our data ...
Dim x As Integer       ' misc counter
Dim i As Integer       ' misc counter
Dim p As Integer       ' misc counter
Dim r As Boolean       ' misc counter
On Error Resume Next
Kill "[URL="file://\\Hr-payroll\ceridian\Alco"]File[/URL].xls"
On Error GoTo 0
Workbooks.Add.SaveAs Filename:= _
        "[URL="file://\\Hr-payroll\ceridian\Alco"]File[/URL]" & _
        ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
strURL = "[URL]https://website[/URL]"
strUsername = "userid"
strPassword = "password"
Set IE1 = New InternetExplorer
Set IE2 = Nothing
With IE1
    .navigate strURL
    .Visible = True
While IE1.Busy: DoEvents: Wend
IE1.Document.getElementById("userId").Value = strUsername
While IE1.Busy: DoEvents: Wend
IE1.Document.getElementById("password").Value = strPassword
While IE1.Busy: DoEvents: Wend
IE1.Document.all("PC_7_2BVS997308NO802RTP4BED2002__login").Click
Application.Wait Now + TimeValue("00:00:07")
strURL = [URL]https://weblink[/URL]
IE1.navigate strURL
Application.Wait Now + TimeValue("00:00:07")
IE1.Document.getElementById("selectitem_PC_7_K6BIU0P2088G002FL9SIKQ2GK5_").Value = "summaryTab"
IE1.Document.all.Item(181).Click
Application.Wait Now + TimeValue("00:00:05")
While IE1.Busy: DoEvents: Wend
IE1.Document.all.Item(183).Click
Application.Wait Now + TimeValue("00:00:05")
While IE1.Busy: DoEvents: Wend
End With
 
'this is the link that pulls up the child window
IE1.Document.all.Item(649).Click
 
'here is where i am attempting to scrape the data from the table in the child window
Set ieDoc = IE2.Document
'Loop through all the elements in the document via the 'all' property
 For i = 0 To ieDoc.all.Length - 1
       ' check that we have the right table ...
       If TypeName(ieDoc.all(i)) = "HTMLTable" And _
       InStr(ieDoc.all(i).innerHTML, "contentTAS") > 0 Then
            Set IeTable = ieDoc.all(i)
            ' loop thru each row in our table
            ' note we skip the first (header) row below ...
            For x = 1 To IeTable.Rows.Length - 1
                ' loop thru the cells in the row ...
                For p = 0 To IeTable.Rows(x).Cells.Length - 1
                    Set ieCell = IeTable.Rows(x).Cells(p)
                    ActiveCell.Offset(x, p).Value = ieCell.innerText
                Next
            Next
        Exit For
        End If
    Next i
End Sub
 
 
Private Sub IE1_NewWindow2(ppDisp As Object, Cancel As Boolean)
    Set IE2 = New InternetExplorer
    Set ppDisp = IE2.Application
    Debug.Print "NewWindow2"
End Sub