[Solved] Is it necessary to declare variable for "htmldocument" twice in a single subroutine



  • Hi there! This is my first post in this forum. I have an issue related to the code i have written.
    Running my code i get 20 links. I like to parse every docs from those links.
    But, when i run my code to get the docs of
    each link it provides the docs of the first link for 20 times, that means it repeats
    the action for a single link not for the 20 links.
    Should i use a different "HTMLDocument" variable if
    within a SINGLE SUBROUTINE i try to produce results from those links.
    When i am using the same "HTMLDocument"
    variable i get repetitive result for a single link.
    If i'm not disturbing you much, hope to hear from you soon.

    For you consideration i pasted here a portion of my code written
    within a single subroutine.

    http.Open "GET", x, False
    http.send
    html.body.innerHTML = http.responseText
    Set http = Nothing
    

    -------some code to produce [Y]----------------

    http.Open "GET", y, False
    http.send
    tpm.body.innerHTML = http.responseText
    Set http = Nothing
    

    You perhapas noticed i used above html and tpm to declare
    the "HTMLDocument" differently within a single subroutine:

    First time--html.body.innerHTML = http.responseText
    2nd time--- tpm.body.innerHTML = http.responseText

    ===================================================
    And the full code is:

    Const pageurl As String = "http://www.slg.ch"
    Sub Unique()
    
    Dim xmlpage As New MSXML2.XMLHTTP60
    Dim htmldoc As New MSHTML.HTMLDocument
    Dim htmlas As Object, gist As Object, hh As String
    Dim htmla As Object, s As Long, dd As String
    
    Range("A1").Select
    
    xmlpage.Open "GET", "http://www.slg.ch/de/branchenverzeichnis/liste", False
    xmlpage.send
    htmldoc.body.innerHTML = xmlpage.responseText
    Set xmlpage = Nothing
    
    Set htmlas = htmldoc.getElementsByClassName("address_pagination")(0)
    Set gist = htmlas.getElementsByTagName("a")
    For s = 0 To gist.Length - 3
    dd = gist(s).getAttribute("href")
    hh = pageurl & Mid(dd, InStr(dd, ":") + 1)
    
    continuation hh
    Next s
    
    End Sub
    Sub continuation(msmm As String)
    Dim http As New MSXML2.XMLHTTP60, html As New HTMLDocument, hnp As New HTMLDocument
    Dim topics As Object, gist As Object
    Dim x As String, y As String
    Dim b As Long, fist As String
    Dim vids As Object, vid As Object
    
    
    
    http.Open "GET", msmm, False
    http.send
    html.body.innerHTML = http.responseText
    Set http = Nothing
    
    Set topics = html.getElementsByClassName("address_row")
    For b = 0 To topics.Length - 1
    
    If Not topics(b) Is Nothing Then
    
    fist = topics(b).getElementsByTagName("a")(0).getAttribute("href")
        x = fist
        y = pageurl & Mid(x, InStr(x, ":") + 1)
    
    http.Open "GET", y, False
    http.send
    hnp.body.innerHTML = http.responseText
    Set http = Nothing
    
    End If
    
    Set vids = hnp.getElementsByClassName("detail_row_right_cell")
        For Each vid In vids
            ActiveCell.Value = vid.innerText
            ActiveCell.Offset(1, 0).Select
        Next vid
    Next b
    End Sub
    

  • administrators

    Hi @shahin2137

    I went through your code and executed it, What you are doing is totally fine by having 2 HTML Documents.

    If you want to avoid having 2 documents then you can save the links to a collection first then loop through that collection. You can rewrite your code like this -

    Sub continuation(msmm As String)
    Dim http As New MSXML2.XMLHTTP60, html As New HTMLDocument, links As New Collection
    Dim topics As Object, gist As Object
    Dim x As String, y As String
    Dim b As Long, fist As String
    Dim vids As Object, vid As Object
    http.Open "GET", msmm, False
    http.send
    html.body.innerHTML = http.responseText
    Set http = Nothing
    
    Set topics = html.getElementsByClassName("address_row")
    For b = 0 To topics.Length - 1
    
    If Not topics(b) Is Nothing Then
    
    fist = topics(b).getElementsByTagName("a")(0).getAttribute("href")
        x = fist
        y = pageurl & Mid(x, InStr(x, ":") + 1)
        links.Add y
    End If
    Next b
    For Each link In links
    http.Open "GET", link, False
    http.send
    html.body.innerHTML = http.responseText
    Set http = Nothing
    Set vids = html.getElementsByClassName("detail_row_right_cell")
        For Each vid In vids
            ActiveCell.Value = vid.innerText
            ActiveCell.Offset(1, 0).Select
        Next vid
    Next
    End Sub
    


  • Hi ranjith! Thanks for your sharp reply. I didn't see you revised code yet but your sharp response amazed me. Anyways, you perhaps noticed that using a single htmldocument variable all the places within the code, forces the program to bring duplicate results over and over again. That is why i was stuck there for the first time. Anyways, using the single variable i suppose it overwrites the code. By the way, one more thing i would like to ask you that is, could you please give me a clue how can i get sample code for reverse lookup to fetch web data using vba in the same manner i have written my code here. That means , i will have a data set in my excel sheet and the program will fetch related info to the adjacent cells in my spreadsheet. I can't just get any idea. Thanks for everything.



  • @ranjithkumar10
    Thanks man. Hats off. You always come up with something i want. Anyways, i went through your revised code and it is working fine. By the way, i declare link used in the for loop as variant. Ain't it the right approach? I hope you will take a look at what I've requested you to give me a hint about reverse lookup. Thanks for everything.


  • administrators

    You can define range of your cells with input data, loop through the range - get data from cell using cell.value. Use it to call appropriate API or website, get back response, parse it and paste data into adjacent cells using cell.offset method.

    Just give it a try, its pretty simple.


Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.