Web Scraper won't fill in a child window that my VBA code launches

Question

I have the following code in VBA which opens up an IE page, fills it in and then clicks on a button that opens up a new IE window. However, my code is not able to fill in the first dropdown of the new window. Any help would be greatly appreciated.

Sub Van()
    Dim IE As Object
    Dim IE2 As Object

    Set IE = CreateObject("InternetExplorer.Application")
    'Set IE2 = CreateObject("InternetExplorer.Application")

    IE.navigate ("website")
    IE.Visible = True

    Do
        DoEvents
    Loop Until IE.readyState = 4

    Set d = IE.document

    'Code now clicks on buttons or dropdowns etc
    Application.Wait (Now + TimeValue("00:00:03"))

    Set IE2 = GetIE("pop-up page")

    WaitFor IE2

    'Tried this too
    'Application.Wait (Now + TimeValue("00:00:05"))
    IE2.document.getElementsByTagName("select")(0).Value = "X"

    'Also tried the line below
    'IE2.document.GetElementsbyname("name")(0).SelectedIndex = 1

End Sub

Function GetIE(sAddress As String) As Object

    Dim objShell As Object, objShellWindows As Object, o As Object
    Dim retVal As Object, sURL As String

    Set retVal = Nothing
    Set objShell = CreateObject("Shell.Application")
    Set objShellWindows = objShell.Windows

    'see if IE is already open
    For Each o In objShellWindows
        sURL = ""
        On Error Resume Next
        sURL = o.LocationURL
        On Error GoTo 0
        If sURL <> "" Then
            'MsgBox sURL
            If sURL = sAddress Then
                Set retVal = o
                Exit For
            End If
        End If
    Next o

    Set GetIE = retVal
End Function

Sub WaitFor(IE)
    Do
        DoEvents
    Loop Until IE.readyState = 4
End Sub

Do you really have those spaces after the `?` in the URL you're passing to `GetIE` ? — Tim Williams, Sep 03 '15 at 23:37
No, sorry, that's just the way I mistakenly copied it here - I'll edit it — chris, Sep 03 '15 at 23:47

score 1 · Accepted Answer · answered Sep 04 '15 at 00:03

1

The page in the pop-up may not be fully loaded when you're trying to access the select - try adding a wait loop until loading is done. I like to pull out the "wait" loop into a re-usable function:

Set IE2 = GetIE("https://secure.apia.com.au/NASApp/apia/CRQuoteServlet?" & _
                "pageAction=openModelSelectionWindow&currentSequenceNumber=")

WaitFor IE2

IE2.document.getElementsByTagName("select")(0).Value = "JAY"

WaitFor:

Sub WaitFor(IE)
    Do
        DoEvents
    Loop Until IE.readystate = 4
End sub

answered Sep 04 '15 at 00:03

Tim Williams

154,628
8
97
125

Thanks Tim - It's still not working for some reason. I've amended my code (please see my original post which has been edited). I've also just tried putting in my usual, less-elegant `Application.Wait (Now + TimeValue("00:00:05"))` type of delay and that doesn't seem to work either. The MsgBox `'MsgBox sURL` does show the correct URL once the comment is removed so it appears like the code of the function works well to at least that point (and so perhaps the function "fails" to do anything after that, such that the delay or waiting time is somewhat redundant)? – chris Sep 04 '15 at 02:08
At this point, if I "un-comment" the MsgBox, I just get the URL of the pop-up in a MsgBox. After clicking "ok", nothing else happens. If I comment out the MsgBox again, the pop-up is generated as usual, the dropdown is not filled in, and no error is generated. – chris Sep 04 '15 at 02:49
Not much else I can suggest - it works for me as-posted (just re-tested). You'll have to do some debugging. – Tim Williams Sep 04 '15 at 03:31
This is really bizarre - It works on my laptop for some reason. I don't know if it's because I have two monitors for my desktop PC and the new window pops up on the second one or if it's because I'd changed the new-tab settings to open a tab in a new window and didn't restart my desktop PC. Thanks very much Tim! Now I can see the light at the end of the tunnel! – chris Sep 04 '15 at 04:14

Web Scraper won't fill in a child window that my VBA code launches

1 Answers1