Excel to Internet Explorer Fun

Hello all,

I have the following code which loads up a webpage and inputs some dates into the forms which works tremendously.  As you can see at the end of the code it puts focus on a button and forces a click.  The result of this click is that it opens up another webpage that I would then like to manipulate in much the same way but am unable to as my VBA only looks at the original openened page.  The question is:

"How can I get my VBA to transfer its attentions to the newly opened page for similar maniuplation"

Code as below which currently works perfectly :)

Sub Get_DWOR_CSL_data()

    Dim i As Long
    Dim ie As Object
    Dim objElement As Object
    Dim objCollection As Object
 
    ' Create InternetExplorer Object
    Set ie = CreateObject("InternetExplorer.Application")
 
    ' You can uncoment Next line To see form results
    ie.Visible = True
    
    ' Maximize the window (uses function at the top of the page)
    apiShowWindow ie.hwnd, SW_MAXIMIZE
 
    ' Send the form data To URL As POST binary request
    ie.Navigate "http://dwor/DWOR+/Pages/ReportViewer.aspx?Rpt=rptCostedReview"
    
    While ie.busy
        DoEvents  'wait until IE is done loading page.
    Wend
    
    ie.Visible = True
 
'My Parameter (5093 = Days, 5094 = Eves, 5095 = Nights)
    ie.Document.All("ctl00$MainContent$ddlRptParamSetID").Value = "5094"
    While ie.busy
      DoEvents  'wait until IE is done loading page.
    Wend
    
    
    ie.Document.All("ctl00$MainContent$txtDateFrom").Value = Format(Range("C3").Value, "dd/mm/yyyy")
    While ie.busy
      DoEvents  'wait until IE is done loading page.
    Wend
    ie.Document.All("ctl00$MainContent$txtDateTo").Value = Format(Range("C3").Value, "dd/mm/yyyy")
    While ie.busy
      DoEvents  'wait until IE is done loading page.
    Wend
    
    ie.Document.All("ctl00$MainContent$btnShowReport").Focus
    ie.Document.All("ctl00$MainContent$btnShowReport").Click
 
    'Application.Wait Now() + TimeValue("00:00:10")


End Sub

Open in new window

Canders_12Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Well I've got something that could work but all I could do is post an example, you'd have to adapt it yourself.

Not even sure if the URL in the sample code is valid anymore.

Perhaps if I post the barebones of the code it might, it's pretty rough though.

Put this in a class module called Class1.
Option Explicit

Public WithEvents IE As InternetExplorer


Private Sub IE_NewWindow2(ppDisp As Object, Cancel As Boolean)
    If IE2 Is Nothing Then

        Set IE2 = New InternetExplorer
        Set ppDisp = IE2.Application
        Debug.Print "NewWindow2"
    End If
End Sub

Open in new window

Put this in a 'standard module'

Option Explicit
Public IE2 As InternetExplorer


Private Sub ParseReportFromWeb()
Dim wsNew As Worksheet

Dim sURL As String
Dim ElementCol As IHTMLElementCollection
Dim link As HTMLAnchorElement
Dim doc As HTMLDocument
Dim doc2 As HTMLDocument

Dim frm As HTMLFormElement
Dim lnkOverRide As HTMLLinkElement

Dim IE1 As InternetExplorer
Dim c As New Class1


    'Example web site which has links which open in a new window
    sURL = "StartURL"

    Set IE1 = New InternetExplorer

    Set c.IE = IE1

    Set IE2 = Nothing

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

        ' loop until the page finishes loading
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop

        Set doc = .document

        ' do stuff on first page
        Set frm = doc.forms(0)


        doc.getElementById("field1").Value = "field1"
        doc.getElementById("field2").Value = "field2"
        doc.getElementById("field3").Value = "field3"

        ' this is the part where the new window would open
        frm.submit
    End With
    
        'Ensure new window has been created
        Do While IE2 Is Nothing: DoEvents: Loop

        ' should now have a reference to the page in the popupwindow
        ' so do stuff with it

        Do While IE2.Busy: DoEvents: Loop
        Do While IE2.readyState <> 4: DoEvents: Loop

        Set doc2 = IE2.document

        ' do stuff with 2nd page

        'close both pages
    
        IE2.Quit

        Set IE2 = Nothing

        IE1.Quit

        Set IE1 = Nothing

    End Sub

Open in new window

Hopefully you can follow that.

I can post the original example, just need to check it works - or find an example that does.
0
 
NorieVBA ExpertCommented:
Is it a new page or a new window?

If it's a new page there's no problem, that's the the page now loaded in IE and you can refer to it just as you did the last page, which is no longer loaded in IE.

0
 
Canders_12Author Commented:
imnorie:

Sadly it is a new window option and I have no way of amending this in internet explorer.  Any further cunning ideas?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Canders_12Author Commented:
I appreciate the code above, however, I fall at the first hurdle:
Public IE2 As InternetExplorer

gives me an "uder-defined type not defined" compile error :/
0
 
NorieVBA ExpertCommented:
Add a reference for Microsoft Internet Controls and Microsoft HTML Object Library.
0
 
Rob HensonFinance AnalystCommented:
Does Right Click and New tab work?

If so maybe you could force a RIght Click instead.

Thanks
Rob H
0
 
NorieVBA ExpertCommented:
Rob

Is there a way to do that in code?
0
 
Canders_12Author Commented:
robhenson:

Good shout but it is a button and right clicking gives no options :(

imnorie:

I have a meeting this morning but I will work on your solution this afternoon :)

Many thanks to you both for throwing some ideas out there.

Many thanks.
0
 
Canders_12Author Commented:
Many many thanks, Alan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.