• 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...

  • 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?

shahin

shahin

Active Member
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: 
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.
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    7.8 KB · Views: 7
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: 
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
 
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.
 
Found another way to achieve that. Take a look sir:
Code: 
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: 
Microsoft Shell Controls And Automation

And it successfully scrapes the value as well focusing on the new window.
 
Right you are sir. However, I get frightened when I see any subroutine wrapped within a function.
 
shahin said:
Right you are sir. However, I get frightened when I see any subroutine wrapped within a function.
Click to expand...
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/
 
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: 
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.
 
Hi !

All is just an object from Document Object Model
as you can - must ! - inspect yourself under IE (or htmlfile)
via VBA Locals window …
​
 
FYI - Also, it's legacy item and generally not recommended for use. Though, shouldn't really matter for scraping.

From MDN...
Document.all
Provides access to all elements in the document. This is a legacy, non-standard interface and should not be used.
Click to expand...
 
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: 
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: 
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
 
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.
 
@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.
 
Select entire loop code. Copy, paste after "Set" line...
Code: 
    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: 
    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.
 
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.
 
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?
 
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.
 
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.
 
Chihiro said:
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.
Click to expand...
As VBA does not well release memory for object variables sometimes,
better is to free this variable kind before procedure ends …
​
 
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: 
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?
 
I think no need of using Application.Wait as this line is enough
Code: 
Do While .Busy = True Or .readyState < 4: DoEvents: Loop

This line ensures that nothing will happen unless the page is loaded ..
 
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.
 
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.
 
You must log in or register to reply here.
Back
Top