1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get data from a new tab (window) created automatically while working with another?

Discussion in 'VBA Macros' started by shahin, Jan 22, 2018.

  1. shahin

    shahin Active Member

    Messages:
    882
    I've created a macro which is able to click few links on a webpage to unhide a search-box and after filling the search-box with required number, when I click on the go button to display the data, I can successfully do that as well. However, the problem is the required data get displayed in a new tab. This is a brand new problem to me and I can't deal with it anyway. However, the only hint I got is that I need to make my script focus on that new window. I don't know how to do that. I've already set variable to grab the data but I'm getting every time the same error when my script hits the "Debug.print" line and the error is "Application-defined or object-defined error". How can I reach the data by focusing on the new tab? Thanks in advance.

    Here is the script:
    Code (vb):

    Sub Grab_Data()

        Dim post As Object
       
        With CreateObject("InternetExplorer.Application")
            .Visible = True
            .navigate "http://www.trf5.jus.br/cp/"
            While .Busy = True Or .readyState < 4: DoEvents: Wend
           
            .document.getElementById("tipo_xmlrpvprec").Click
            .document.getElementById("tipo_xmlprec").Click
            .document.getElementById("filtroRPV_Precatorios").Value = "160340"
            .document.getElementById("submitConsulta").Click
           
            ''after performing the click a new window opens up here
           ''tried waiting here
         
            Do
                Set post = .document.getElementsByClassName("linkar")
                DoEvents
            Loop While post Is Nothing
           
            ''tried waiting here as well
         
            Debug.Print post(0).innerText
        End With
       
    End Sub
     
    I'm uploading an Image to show you which field/fields I'm after. The image is taken from the new window.

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,751
    You need Shell.Application to access second tab. As IE (as far as I know) doesn't expose Tab as accessible element through API.

    Try something like...
    Code (vb):
    Sub Grab_Data()

        Dim post As Object
        Dim ie As Object
     
        With CreateObject("InternetExplorer.Application")
            .Visible = True
            .navigate "http://www.trf5.jus.br/cp/"
            While .busy = True Or .readystate < 4: DoEvents: Wend
         
            .Document.getElementById("tipo_xmlrpvprec").Click
            .Document.getElementById("tipo_xmlprec").Click
            .Document.getElementById("filtroRPV_Precatorios").Value = "160340"
            .Document.getElementById("submitConsulta").Click
           
            Set ie = GetIETab("http://www.trf5.jus.br/cp/cp.do")
            Debug.Print ie.Document.getElementsByClassName("linkar")(0).innerText
            ie.Quit
            .Quit
        End With
       
     
    End Sub
    Function GetIETab(sLocation As String) As Object

    Dim oShell As Object, oShellWin As Object, obj As Object
    Dim sURL As String
    Dim oTab As Object

        Set oTab = Nothing
        Set oShell = CreateObject("Shell.Application")
        Set oShellWin = oShell.Windows
       
        For Each obj In oShellWin
            sURL = ""
            While obj.readystate < 4 Or obj.busy: DoEvents: Wend
            On Error Resume Next
            sURL = obj.Document.Location
            On Error GoTo 0
            If sURL Like sLocation & "*" Then
                Set oTab = obj
                Exit For
            End If
        Next

    Set GetIETab = oTab

    End Function
    YasserKhalil and shahin like this.
  3. shahin

    shahin Active Member

    Messages:
    882
    Thanks sir for your solution. Your provided script works like magic as always. I just tested it. I get frightened this time to see your code. I need to study on this. Thanks a lot sir.
  4. shahin

    shahin Active Member

    Messages:
    882
    Found another way to achieve that. Take a look sir:
    Code (vb):

    Sub Grab_Data()

        Dim post As Object, IE As New InternetExplorer
        Dim winShell As Shell, HTML As HTMLDocument
       
        With IE
            .Visible = True
            .navigate "http://www.trf5.jus.br/cp/"
            While .Busy = True Or .readyState < 4: DoEvents: Wend
            Set HTML = .document
        End With
           
        HTML.getElementById("tipo_xmlrpvprec").Click
        HTML.getElementById("tipo_xmlprec").Click
        HTML.getElementById("filtroRPV_Precatorios").Value = "160340"
        HTML.getElementById("submitConsulta").Click
       
        Set winShell = New Shell
        For Each IE In winShell.Windows
            If InStr(1, IE.LocationURL, "cp.do", vbString) > 0 Then
                IE.Visible = True
                While IE.Busy = True Or IE.readyState < 4: DoEvents: Wend
                Exit For
            End If
        Next
       
        Set HTML = IE.document
       
        Set post = HTML.getElementsByClassName("linkar")
        Debug.Print post(0).innerText
       
        IE.Quit
    End Sub
     
    Reference to add to the library:
    Code (vb):

    Microsoft Shell Controls And Automation
     
    And it successfully scrapes the value as well focusing on the new window.
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,751
    Basically same thing. I just did it in separate function.
    shahin likes this.
  6. shahin

    shahin Active Member

    Messages:
    882
    Right you are sir. However, I get frightened when I see any subroutine wrapped within a function.
  7. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,882
    There's no reason to get frightened.

    In fact, such routines and functions can save you from repetitive coding which helps in
    - cutting down code writing time (you write once)
    - updates are easier as you update at one place and change is implemented at all places.

    So if you write such modular codes then maintainability is enhanced several times.

    Here's one discussion that happened a while ago:
    https://chandoo.org/forum/threads/h...rksheets-from-workbook-and-change-font.36431/
    shahin likes this.
  8. shahin

    shahin Active Member

    Messages:
    882
    If Marc L or sir Chihiro comes across this post, I may get the answer I'm looking for. Around a year back in replying to one of my question, Marc L provided me with a piece of code to solved a particular issue. Today while reading different posts, I found that script. However, I can't understand a line within this code "MsgBox .all("left").-------". If I knew how it worked, I would be very happy. To be clearer: I only wish to know how ".all()" works in this case?

    Code (vb):

    Sub Demo()
       WithCreateObject("Msxml2.XMLHTTP")
            .Open"GET", "http://www.whitepages.com/phone/1-323-871-0143", False
            .setRequestHeader "DNT", "1"
             OnErrorResumeNext
            .send
             OnErrorGoTo 0
             If .Status <> 200 Then Beep: ExitSubElse T$ = .responseText
       EndWith
       WithCreateObject("htmlfile")
            .write T
            MsgBox .all("left").FirstChild.FirstChild.Children(1).Children(1).innerText
       EndWith
    EndSub
     
    If I choose to go with xmlhttp requests, then I usually use "html.getelementsby----" (if "html.body.innerhtml = http.responstext"). But in the above code block, I could not understand what ".all()." doing here and what might be the "fully qualified line of code" to get the clarity. Thanks in advance.
  9. Marc L

    Marc L Excel Ninja

    Messages:
    4,046
    Hi !

    All is just an object from Document Object Model
    as you can - must ! - inspect yourself under IE (or htmlfile)
    via VBA Locals window …
    shahin likes this.
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,751
    FYI - Also, it's legacy item and generally not recommended for use. Though, shouldn't really matter for scraping.

    From MDN...
    shahin likes this.
  11. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    726
    That's what Ninja's do. They induce fear! :DD
  12. shahin

    shahin Active Member

    Messages:
    882
    If I try like below, I get the first movie name from that webpage which is how it should be. However, I was wondering If I could create a loop within the "With CreateObject("htmlfile"): End With" block and get all the movies. Is it possible at all?

    Not like this (It's fetching all the movie names, though):

    Code (vb):

    Sub Scraping_Tags()
        Dim posts As Object, post As Object
     
        With CreateObject("Msxml2.XMLHTTP")
            .Open "GET", "https://www.yify-torrent.org/search/1080p/", False
            .setRequestHeader "DNT", "1"
             On Error Resume Next
            .send
             On Error GoTo 0
             If .Status <> 200 Then Beep: Exit Sub Else T$ = .responseText
        End With
        With CreateObject("htmlfile")
            .write T
            Set posts = .getElementsByTagName("h3")
        End With
     
        For Each post In posts
            Debug.Print post.getElementsByTagName("a")(0).innerText
        Next post
    End Sub
     
    But, like the below one (fetching only the first movie at this moment):
    Code (vb):

    Sub Scraping_Tags()
        With CreateObject("Msxml2.XMLHTTP")
            .Open "GET", "https://www.yify-torrent.org/search/1080p/", False
            .setRequestHeader "DNT", "1"
             On Error Resume Next
            .send
             On Error GoTo 0
             If .Status <> 200 Then Beep: Exit Sub Else T$ = .responseText
        End With
        With CreateObject("htmlfile")  '''creating any loop within this block to iterate through the elements and get all the movies
           .write T
            Debug.Print .getElementsByTagName("h3")(0).getElementsByTagName("a")(0).innerText
        End With
    End Sub
     
  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,751
    What's the point though? It really makes no difference whether it's inside With/End With block or outside.

    If you really want to, just copy the entire loop inside With statement after you set the object.

    Or just skip set and For Each Post in .getElementsByTagName("h3")... loop.
    shahin likes this.
  14. shahin

    shahin Active Member

    Messages:
    882
    @sir chihiro, I dare to ask silly questions because you people (benevolent) are around and always ready to help. I can't really find any way sir as to how I can fit the loop within "with" block. Thanks.
  15. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,751
    Select entire loop code. Copy, paste after "Set" line...
    Code (vb):

        With CreateObject("htmlfile")
            .write T
            Set posts = .getElementsByTagName("h3")
            For Each post In posts
                Debug.Print post.getElementsByTagName("a")(0).innerText
            Next post
        End With
     
    Or as I stated...
    Code (vb):
        With CreateObject("htmlfile")
            .write T
            For Each post In .getElementsByTagName("h3")
                Debug.Print post.getElementsByTagName("a")(0).innerText
            Next post
        End With
    Though I don't recommend this, unless you don't need any sort of error trapping or exception handling.
    shahin likes this.
  16. shahin

    shahin Active Member

    Messages:
    882
    So damn nice. I have been dreaming about this type of code block for the last few months. You made my dream true sir. Much obliged. Why I wanted to do this is because I'm a very lazy person to write an extra line unless it is seriously necessary. The only barrier with this method is that I may not be able to use class names.
  17. shahin

    shahin Active Member

    Messages:
    882
    One last thing sir: If I declare "Set post = Nothing" at the end, is there anything I need to do to make this script foolproof?
  18. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,751
    In VBA, you don't need to Set post = Nothing, unless you are strapped for resource and need to release reference as soon as possible.

    VBA garbage collector will release resources as soon as reference counter reaches zero. In most cases, as variable goes out of scope.

    One such example is ADO objects. Typically speaking, you have connection and recordset as object in such code. Recordset is expensive since it holds all the data queried. You tend to clear it as soon as it's not needed.

    Have a read of link below for more detailed exploration into the subject.
    https://blogs.msdn.microsoft.com/er...n-are-you-required-to-set-objects-to-nothing/

    "Fool Proof" is usually difficult to do without extensive testing and ensuring error trapping and exception handling are built in along each step. To me, this boils down to use case and ROI. If I'm the only person running the code... I hardly ever consider "Fool Proofing" code. I can quickly fix issue as I encounter them.

    If the code part of project that's going to be used by others. Then I'd have debug process and feedback cycle built into development cycle.
    GraH - Guido and shahin like this.
  19. shahin

    shahin Active Member

    Messages:
    882
    Thanks a zillion for your in-depth insight sir. Every time I go through the coding style in different forums including StackOverflow, the ones practiced here are more elegant in it's outlook and performance. That is why I feel proud to follow the coding guideline maintained here. I'm very glad to be a member of this forum.
  20. Marc L

    Marc L Excel Ninja

    Messages:
    4,046
    As VBA does not well release memory for object variables sometimes,
    better is to free this variable kind before procedure ends …
  21. shahin

    shahin Active Member

    Messages:
    882
    Although the script I'm gonna talk about doesn't relate to the title of this thread, I'm pasting the code anyway. All I need to know where should I put a specific line within my script. I tried with both the ways (after "Set iedoc = .document" and before "Set iedoc = .document") and found both of them working.

    Code (vb):

    Sub roulette()
        Dim IE As New InternetExplorer, iedoc As HTMLDocument
       
        With IE
            .Visible = False
            .navigate "https://greenbet.info/en/display/fortune"
            Do While .Busy = True Or .readyState < 4: DoEvents: Loop
    '        Application.Wait Now + TimeValue("0:00:05") ''is it supposed to be here
           Set iedoc = .document
        End With
        Application.Wait Now + TimeValue("0:00:05")   '''or here
     
        For Each posts In iedoc.getElementsByClassName("fortune_roulette")
            Row = Row + 1: Cells(Row, 1) = posts.getElementsByClassName("fortune_round__num")(0).innerText
            Cells(Row, 2) = posts.getElementsByClassName("second_digit")(0).innerText
            Cells(Row, 3) = posts.getElementsByClassName("first_digit")(0).innerText
        Next posts
       
        IE.Quit
    End Sub
     
    Which way I should go with?
  22. YasserKhalil

    YasserKhalil Active Member

    Messages:
    968
    I think no need of using Application.Wait as this line is enough
    Code (vb):
    Do While .Busy = True Or .readyState < 4: DoEvents: Loop
    This line ensures that nothing will happen unless the page is loaded ..
  23. shahin

    shahin Active Member

    Messages:
    882
    Hey Yasser, it's a pseudo code. I just wanna know the exact positioning of that delay, in case I deal with a javascript heavy site which loads it's content very slowly. My question again is: should I put that delay after this "Set iedoc = .document" line or before "Set iedoc = .document" this line. Thanks.
  24. YasserKhalil

    YasserKhalil Active Member

    Messages:
    968
    I don't know exactly but I think in this case would be before this line
    Code (vb):
    Set iedoc = .document
  25. shahin

    shahin Active Member

    Messages:
    882
    Me too!! Logically it should be so. However, I don't know for sure. Hope I will have any respected Ninja's opinion as to which way I should stick to.

Share This Page