VBA code to open IE and switch to new tab

[cb366374]

I am stuck...obviously. I was tasked with gathering some data on a long list of companies for my supervisor (I'm a grad assistant) from a messy website (i.e. no specific URLs for each report). I wrote a "simple" program to open internet explorer, navigate through a disclaimer page, change the value of a drop-down, wait for the page to automatically update, then click a link that launches a report. My plan was to simply copy the page that is launched and paste it into a worksheet and find the relevant data I need after that. The website turned out to be a nightmare but I managed to launch the report I had specified. However, the report is launched in a new tab and I have no idea how to navigate there.

From what I have read I believe I need to re-write the code using a shell extension but I have no experience with this and was hoping someone could help. I could probably figure it out eventually by teaching myself some of this new programming but it seems like a large task to complete before Monday (spreadsheet is due). Also, this will be a one-time assignment so I'm looking for something as simple as possible and all the other examples seem too complicated. My current code is below. I have hacked it to pieces trying to figure this out so my apologies for messy code. The shell is my main issue but if someone also has a suggestion gathering the information from the final report more efficiently, I would love suggestions. Thanks in advance.

FYI...
Windows 7, Excel 2010, Internet Explorer 9

Sub gather()
'
    Dim ie As Object
    Dim ElementCol As Object
    Dim btnInput As Object
    Dim linkElement As Object
    Dim Link As Object
    Dim wait As Long
    Dim i As Integer
    Dim compCode As String
    Set ie = CreateObject("internetexplorer.application")
    
    ie.Visible = True
    ie.navigate "https://i-site.naic.org/grid/gridDisc.jsp"
    
    While ie.Busy
        DoEvents
    Wend
    
    Set ElementCol = ie.document.getElementsByTagName("input")
 
    'loop through all 'input' elements and find the one with the value "I Agree"
    For Each btnInput In ElementCol
        If btnInput.Value = "I Agree" Then
            btnInput.Click
            Exit For
        End If
    Next btnInput
    
    While ie.Busy
        DoEvents
    Wend
    
    ie.document.getElementById("Company").Value = "60994"
    
    While ie.Busy
        DoEvents
    Wend
    
    ie.document.forms("criteria").Item("COMPANY").FireEvent ("onchange")
    
    Application.wait Time + TimeSerial(0, 0, 10)
    
    While ie.Busy
        DoEvents
    Wend
    
    ' click a text link on the page after that
    Set linkElement = ie.document.getElementsByTagName("a")
 
    For Each Link In linkElement
        If Link.href = "javascript:fnSubmit()" Then
            Link.Click
            Exit For
        End If
    Next Link
    
    While ie.Busy
        DoEvents
    Wend
        
    ie.ExecWB 17, 0 'SelectAll
    ie.ExecWB 12, 2 'Copy selection
    
    Sheets("Output").Select
    Cells.ClearContents
    Range("A1").Select
    ActiveSheet.Paste
    
End Sub

 

[cb366374]

Bump

 

[Norie]

When I run that code the report appears in a new window, not a tab.

Mind you before I could get it to appear it was blocked as a pop-up, but once I allowed pop-ups it was OK.

Also, the setting in my IE9 is for pop-ups to always appear in a new window.

Perhaps worth a look at your settings for how to display pages.

 

[cb366374]

A new window would not change the issue as I understand it. I am unable to access the new window/tab with the code as it's currently written. Wouldn't I still need a shell? That's really what I'm stuck on. If there is code to switch to the new window I can change my IE settings easy enough.

 

[Norie]

You can use code to capture the new window, but it doesn't work if it's a new tab.

Once you've captured the new window you can get the data from it.

 

[Norie]

I set up some code to get the data from the new page but while after testing it quite a few times I'm now told I've exceeded some sort of session download limit.

So I can't test it anymore, but it was working the last few times I tried it - I was testing it after that when I got the message.

 

[cb366374]

That's not good news. Last time I checked there were over 1335 companies I will have to download

 

[Norie]

It could just be because of where I'm connecting from or how frequently I was running the code when testing.

Actually just tested it and got through to the report page.

However what pasted wasn't from the page.

Not sure why that is.

Right, put in a delay and it worked but I really don't know how reliable the code is.

Don't really want to keep on testing because I'm pretty sure the exceeded download limit will come up.

I can post the code but it's kind of cannablized from another code, and there are certain references, eg Microsoft Internet Controls, you need to add.

Here's the workbook http://www.box.com/s/9iynubj6dypkll4bu6ey if you want to try it for yourself.

 

[cb366374]

I tried it once and it worked perfectly. I won't get too excited quite yet because I still have to search each report for the relevant data and successfully add the loop that will change the company value but those should be fairly straight forward (assuming that limit doesn't come up).

I really appreciate it. I will post updates.

 

[Norie]

If you only want to capture certain data from the report you should look into grabbing it via the document object rather than pasting the whole page into Excel.

To do that you could use some of the methods you've used like getElementByID, getElementsByTagname.

Taking a look at the HTML behind the page should help with that.

Should be quite straightforward as long as the page is well organised and doesn't change for each company.