Connect to data after having found an already open IE window using shell application

Question

VBA code to interact with specific IE window that is already open

Above is a thread to find and go to an already open instance of IE using shell applications in VBA. AFTER I found the open IE instance I am looking for, I need to query the tables from that IE page without using it's URL. The reason that I cannot use it's URL is that this IE page is a generic 'result' page that opens in a separate window after doing a search on the main website, so if I use the URL of the result page, which is: https://a836-acris.nyc.gov/DS/DocumentSearch/BBLResult, it will return an error. Are there any other methods that allow querying tables without using URL connections, like a "getElements" for tables?

K.Davis, Tim William: you are correct in your assumptions. The first part of my code/project opens up a search page: objIE.navigate "https://a836-acris.nyc.gov/DS/DocumentSearch/BBL" and through it I submit a search form. The second part (outlined above in the first paragraph) opens up a result page (pop-up). I am trying to automate the retrieving of the tables from that page. I tried using QueryTables.Add method, the way I am familiar with to connect to the data/webpage requires an URL. If I use the URL from the result page it returns an error, thus I am looking for suggestions/help on how I could otherwise connect. That said I am able to retrieve elements of the page using 'getElements' method but not able to query tables. There are other ways to connect to the data source using the QueryTables.Add method, see, https://learn.microsoft.com/en-us/office/vba/api/excel.querytables.add but I am not familiar with these other methods. Hope this clarifies a bit.

There should be no problems using the same methods you'd normally use - how exactly did you try, and what errors did you run into? — Tim Williams, Oct 23 '18 at 23:20
When I attempt to open your link, I get redirected to `/DS/DocumentSearch/Error` - which is just an error page. This leads me to believe that your issue is that you've set a reference to one IE object, and upon performing some action with that object another IE instance (popup) opens up which I presume to be the link you provided - and you are having troubles setting a reference to that new object? You cannot use the same IE reference if a new window opens up - but your question is difficult to follow, so correct me if I am wrong. — K.Dᴀᴠɪs, Oct 23 '18 at 23:30
I haven't explored selenium yet. Actually recently signed up for an online class that covers it(in part), but haven't had the time to go through it yet. Is selenium a better way to scrape? or just a good adjunct to IE? Thanks. — Skeys, Oct 24 '18 at 12:24

QHarr · Accepted Answer · 2018-10-24T12:27:51.730

I haven't experienced a problem with this as although you have an intermediate window the final IE window resolves to being the main IE window with focus. I was able to grab the results table with the following code using the indicated search parameters:

Option Explicit
Public Sub GetInfo()
    Dim IE As New InternetExplorer
    With IE
        .Visible = True
        .navigate "https://a836-acris.nyc.gov/DS/DocumentSearch/BBL"

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document
            .querySelector("option[value='3']").Selected = True
            .querySelector("[name=edt_block]").Value = 1
            .querySelector("[name=edt_lot]").Value = "0000"
            .querySelector("[name=Submit2]").Click
        End With

        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim hTable As HTMLTable

        Set hTable = .document.getElementsByTagName("table")(6)

        'do stuff with table

        .Quit
    End With
End Sub

You can copy a table via clipboard. Any tick windings appear in the right place but as empty icons.

For clipboard early bound go VBE > Tools > References > Microsoft-Forms 2.0 Object Library.

If you add a UserForm to your project, the library will get automatically added.

Dim clipboard As DataObject
Set clipboard = New DataObject
clipboard.SetText hTable.outerHTML
clipboard.PutInClipboard
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).PasteSpecial

Late bound use

Dim clipboard As Object
Set clipboard =  GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

Connect to data after having found an already open IE window using shell application

1 Answers1