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.

Can't parse name from a webpage using POST request

Discussion in 'VBA Macros' started by shahin, Aug 12, 2017.

  1. shahin

    shahin Active Member

    Messages:
    479
    I've written a macro in vba to get a "name" from a website using POST request. To reach the target page it is necessary to send POST request twice. Firstly, a page opens up like the first image. After clicking on the "search by address" button it leads to another page where two boxes to be filled in. One for street number and the other for street name. After clicking on the search button when the form is done filling then it leads to the target page with the information i'm after. I tested it using msgbox in the script to be sure i'm on the right page. I'm surely on that page and i can see the title of that page which is "HARRIS COUNTY APPRAISAL DISTRICT". However, I can't parse anything from that page. I'm after this name "LARA PEDRO A & MARIA G" from that page.

    This is the macro I'm trying with:
    Code (vb):

        Sub httpPost()
     
            Dim http As New XMLHTTP60, html As New HTMLDocument
            Dim ArgStr As String, ArgStr_ano As String
       
            ArgStr = "search=addr"
            ArgStr_ano = "TaxYear=2017&stnum=15535&stname=CAMPDEN+HILL+RD"
       
            With http
                .Open "POST", "https://public.hcad.org/records/QuickSearch.asp", False
                .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
                .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36"
                .setRequestHeader "Referer", "https://public.hcad.org/records/quicksearch.asp"
                .send ArgStr
            End With
     
            With http
                .Open "POST", "https://public.hcad.org/records/QuickRecord.asp", False
                .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
                .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36"
                .setRequestHeader "Referer", "https://public.hcad.org/records/quicksearch.asp"
                .send ArgStr_ano
                html.body.innerHTML = .responseText
            End With
         
            MsgBox http.responseText
        End Sub
     
    There is a table in that page whose html elements I'm attaching below for your consideration:

    Code (vb):


        <td class="data" valign="top" nowrap="">
                                         
                                                <table width="350">
                                             
                                                    <tbody><tr>  
                                                        <td valign="top" nowrap="">Owner Name &amp;<br>Mailing Address:</td>
                                                        <th align="left" valign="top" nowrap="">
                                                     
                                                        <!-- ---------- OWNER NAME ---------- -->
                                                        LARA PEDRO A &amp; MARIA G<br>
                                                       
     
                                                        <!-- ---------- MAILING ADDRESS ---------- -->
                                                     
                                                                <!-- ---------- MAILING ADDRESS (MAIL TO) ---------- -->
                                                             
     
                                                                <!-- ---------- MAILING ADDRESS (ADDR1 AND ADDR2) ---------- -->
                                                                15531 CAMPDEN HILL RD<br>
                                                             
                                                     
                                                                <!--
                                                                ------------------------------------------------
                                                                'RA 09/11/2012:
                                                                   Changed order of Owner / Address to :
     
                                                                        Owner Name(s)
                                                                        MailTo
                                                                        Addr_1
                                                                        Addr_2
                                                                        City
                                                                        State
                                                                        Zip
                                                                        Country
                                                                ------------------------------------------------
                                                                15531 CAMPDEN HILL RD
                                                                ------------------------------------------------
                                                                -->
     
                                                                <!-- ---------- MAILING ADDRESS (CITY-STATE-ZIP OR COUNTRY)---------- -->
                                                                HOUSTON&nbsp;TX&nbsp;77053-3302<br>
                                                             
                                                                    <!--<br />-->
                                                             
                                                     
                                                       </th>
                                                    </tr>
                                                </tbody></table>
       
                                            </td>

     

    Search to be made with:
    Code (vb):

        Street No: 15535
        Street Name: CAMPDEN HILL RD
     
    These are the image of two pages following which target page can be reached:
    "https://www.dropbox.com/s/e9on9zwqzmcboze/1Untitled.jpg?dl=0"
    "https://www.dropbox.com/s/axd66fvozexbefp/Untitled.jpg?dl=0"
  2. shahin

    shahin Active Member

    Messages:
    479
    Btw, is it at least possible to get the existing url in vba if i try to check putting any appropriate command right before the "End Sub" line? I tried to check msgbox http.URL but it threw an error.
  3. shahin

    shahin Active Member

    Messages:
    479
    I somehow caught the url using chrome developer tool and using that url in my below code I get the result I'm after. However, what's wrong with my "POST" request? Why can't I get the same using the above method? For your consideration, here is another bit of code to get the result using the collected url from chrome dev tool what i got by sending post request twice as i did in my above code:
    Code (vb):

    Sub Web_Data()
        Dim http As New XMLHTTP60, html As New HTMLDocument
        Dim post As Object

        With http
            .Open "GET", "https://public.hcad.org/records/details.asp?crypt=%94%9A%B0%94%BFg%85%8D%83%82og%8El%87tXvXQJXJzDTpHjEyr%D4%BE%C2%AF%AE%AA%9Fpk%88%5Do%5B%B8%96%A3%C0q%5E&bld=1&tab=", False
            .send
            html.body.innerHTML = .responseText
        End With

        For Each post In html.getElementsByClassName("data")(2).getElementsByTagName("th")
            i = i + 1: Cells(i, 1) = post.innerText
        Next post
    End Sub
     
    The result is:
    LARA PEDRO A & MARIA G
    15531 CAMPDEN HILL RD
    HOUSTON TX 77053-3302
  4. shahin

    shahin Active Member

    Messages:
    479
    Finally solved it using winhttprequest by enabling redirect:
    Code (vb):

    Sub httpPost()

        Dim http As New WinHttp.WinHttpRequest, html As New HTMLDocument
        Dim post As Object
        Dim ArgStr As String, ArgStr_ano As String

        ArgStr = "search=addr"
        ArgStr_ano = "TaxYear=2017&stnum=15535&stname=CAMPDEN+HILL+RD"

        With http
            .Option(6) = True
            .Open "POST", "https://public.hcad.org/records/QuickSearch.asp", False
            .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
            .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36"
            .setRequestHeader "Referer", "https://public.hcad.org/records/quicksearch.asp"
            .send ArgStr
        End With

        With http
            .Option(6) = True
            .Open "POST", "https://public.hcad.org/records/QuickRecord.asp", False
            .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
            .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36"
            .setRequestHeader "Referer", "https://public.hcad.org/records/quicksearch.asp"
            .send ArgStr_ano
            html.body.innerHTML = .responseText
        End With

        For Each post In html.getElementsByClassName("data")(2).getElementsByTagName("th")
            i = i + 1: Cells(i, 1) = post.innerText
        Next post

    End Sub
     
    Marc L and (deleted user) like this.

Share This Page