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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.