locked
How can I extract innertext from several child URLs that come from a parent URL? RRS feed

  • Question

  • I have the script below, which clicks a link and opens a child URL (from a parent URL).

    If link.innerHTML Like "*Upload Questionnaire*" Then
                link.Click
    
                Set objIE = CreateObject("InternetExplorer.Application")
                objIE.Navigate strURL
    
                Do While objIE.ReadyState <> 4 And objIE.Busy
                   DoEvents
                Loop
    
    
                        Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
                        XMLHTTP.Open "GET", strSQL, False
                        XMLHTTP.send
                        Set html = CreateObject("htmlfile")
                        html.body.innerHTML = XMLHTTP.responseText
                        Set tbl = html.getElementsByTagName("Table")
                        Set tr_coll = tbl(0).getElementsByTagName("TR")
                        For Each tr In tr_coll
                            j = 1
                            Set td_col = tr.getElementsByTagName("TD")
                            For Each td In td_col
                                Cells(row + 1, j).Value = td.innerText
                                j = j + 1
                            Next
                            row = row + 1
                        Next
            End If

    For one thing, the code doesn't pause and wait for the browser to finish loading.

    Do While objIE.ReadyState <> 4 And objIE.Busy
       DoEvents
    Loop

    Also, I'm feeding in a Parent URL, like this:

    strSQL = "https://blah_blah_blah_CampaignID=" & cell.Value

    The line below doesn't work.

    XMLHTTP.Open "GET", strSQL, False

    Somehow I need to pass in the Child URL that opens from the Parent URL. This is the parent URL: strSQL = "https://blah_blah_blah_CampaignID=" & cell.Value

    When: link.Click runs then the Child URL opens, but I don't know how to reference the Child URL. How can I do that?!




    MY BOOK





    • Edited by ryguy72 Saturday, March 4, 2017 12:12 AM
    Friday, February 17, 2017 3:33 PM

All replies

  • Hi,

    Since this issue is not related to Excel object model and you are using VBScript, I suggest you post on the following forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=scripting

    Sorry for any inconvenience.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 20, 2017 7:17 AM
  • This is 100% Excel-related.  I finally figured it out.  The script below works fine for me.

    Sub ListLinks()
    
    'Set a reference to microsoft Internet Controls
    Dim IeApp As InternetExplorer
    Dim sURL As String
    Dim IeDoc As Object
    Dim i As Long
    
    Set IeApp = New InternetExplorer
    
    IeApp.Visible = True
    sURL = "http://www.sharenet.co.za/v3/q_sharelookup.php"
    IeApp.Navigate sURL
    
    Do
    Loop Until IeApp.ReadyState = READYSTATE_COMPLETE
    Set IeDoc = IeApp.Document
        For i = 0 To IeDoc.Links.Length - 1
            Cells(i + 1, 1).Value = IeDoc.Links(i).href
        Next i
    Set IeApp = Nothing
    Call CopyFromURL
    End Sub
    
    
    Public Sub CopyFromURL()
    Dim IE As InternetExplorer, doc As HTMLDocument
    Dim thisClass As IHTMLElement2, thisLink As IHTMLElement
    Dim rng As Range, cell As Range
    Const READYSTATE_COMPLETE As Integer = 4
    Dim TR_col As Object, TR As Object
    Dim TD_col As Object, TD As Object
    Dim row As Long, col As Long
    row = 1
    Set rng = Range("A1:A5")
    For Each cell In rng
    
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Navigate cell
    
        Do While (IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE)
            DoEvents
        Loop
    
        Set TR_col = IE.Document.getElementsByTagName("TR")
    
        For Each TR In TR_col
            Set TD_col = TR.getElementsByTagName("TD")
    
    col = 2
            For Each TD In TD_col
                Cells(row, col) = TD.innerText
                col = col + 1
            Next
            col = 2
            row = row + 1
        Next
    
    Next cell
    IE.Quit
    End Sub


    MY BOOK

    • Proposed as answer by Chenchen Li Tuesday, February 21, 2017 1:15 AM
    Monday, February 20, 2017 3:44 PM
  • It is a piece of good news that you figured it out.

    I suggest you mark it as answer.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 21, 2017 1:17 AM