Can't make my script fetch valid response using "POST" request

[shahin]

I've written a script in vba to get different golf club names from a certain site. The site requires "__VIEWSTATE" and "__EVENTVALIDATION" parameter to be filled in and passed through "POST" request before providing any valid response. If the necessary fields are taken directly from chrome dev tools to decorate the POST parameter then it is very easy to get the response. However, the thing is the aforesaid two fields contain two monster string which I find heavily difficult to rearrange to fit to the VBE.

I've found a technique (probably useful) to arrange the POST request in such a way so that the two fields with giant string can be stored in two variables and passed accordingly to the request parameter. At this point, I've done everything (so far my knowledge goes) to get the response but somewhere along the way I've made mistakes which I can't dig out. If anybody gives a little twitch in my script to make it work, I'll be very grateful. Thanks in advance.

Sub ClubData()
    Const url = "http://www.golf.co.nz/PlayGolf/ClubDirectory.aspx"
    Dim http As New XMLHTTP60, html As New HTMLDocument, postdata As String
    Dim posts As Object, post As Object
    Dim items As Object, item As Object, elem As Object
  
    With http
        .Open "POST", url, 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"
        .send
        html.body.innerHTML = .responseText
    End With
  
    Set posts = html.getElementById("__VIEWSTATE")
    Set post = html.getElementById("__EVENTVALIDATION")
  
    postdata = "ctl00$scm=ctl00$MainContent$pnlFilters|ctl00$MainContent$btnSearch&__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__VIEWSTATE=" & posts.Value & "&__VIEWSTATEGENERATOR=FD74C8C4&__EVENTVALIDATION=" & post.Value & "&ctl00$txtSearchField=SEARCH&ctl00$MyGolfLoginSummary$tbMyGolfLogin_Username=&ctl00$MyGolfLoginSummary$tbMyGolfLogin_Password=&ctl00$MainContent$cbRegion=0&ctl00$MainContent$cbHoleOpt=0&ctl00$MainContent$tbSearch=SEARCH&__ASYNCPOST=true&ctl00$MainContent$btnSearch=GO"

    With http
        .Open "POST", url, 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"
        .send postdata
        html.body.innerHTML = .responseText
    End With

    For Each elem In html.getElementsByTagName("h2")
       x = x + 1: Cells(x, 1) = elem.innerText
    Next elem
End Sub

Btw, to get the data manually from web, all you need to do is press the "GO" button.

Post Script: This is the link "https://chandoo.org/forum/threads/t...-dealing-with-post-request.34357/#post-212130" to the thread in which the problem was taken care of using the monster string.

 

[Chihiro]

Your string encoding is off. You should have it urlencoded.

Ex: $ replaced with %24 etc.

See the unparsed/source string in developer tool.

For URLencoding accommodating for UTF-8.
https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba/

Read through entire thread, as there are multiple options provided.

 

[shahin]

@sir Chihiro, thanks a lot for your response. Every time I come across your suggestion, I learn something new. I never thought of this encoding stuff until now. However, I've tried to rectify the "postdata" parameter but still there might be something I'm missing and that is why I'm not getting the desired result. Btw, this is how I tried to mend the "postdata":

postdata = "ctl00%24scm=ctl00%24MainContent%24pnlFilters%7Cctl00%24MainContent%24btnSearch&__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__VIEWSTATE=" & posts.Value & " &__VIEWSTATEGENERATOR=FD74C8C4&__EVENTVALIDATION=" & post.Value & "&ctl00%24txtSearchField=SEARCH&ctl00%24MyGolfLoginSummary%24tbMyGolfLogin_Username=&ctl00%24MyGolfLoginSummary%24tbMyGolfLogin_Password=&ctl00%24MainContent%24cbRegion=0&ctl00%24MainContent%24cbHoleOpt=0&ctl00%24MainContent%24tbSearch=SEARCH&__ASYNCPOST=true&ctl00%24MainContent%24btnSearch=GO"

 

[Chihiro]

What's contained in post.Value?

It that also URL encoded?

Edit: I think you've got unnecessary space in front of "&_VIEWSTATEGENERATOR=..."

 

[shahin]

Right you are, sir. The "post.value" and "posts.value" are not URL encoded and I don't have any idea how to do so. I've adjusted the spacing, though.

 

[Chihiro]

Read the link I gave you. That explains methods to URL encode the string.

 

[shahin]

Oh my god!!! I really can't believe that I've got it working finally. It was indeed the encoding issue that I fixed using "EncodedUrl = WorksheetFunction.EncodeUrl(InputString)" function which I've found out following the link provided by sir Chihiro. Thanks a lot sir to walk me through the solution.

Here is the working code (name and address):

Sub ClubData()
    Const url = "http://www.golf.co.nz/PlayGolf/ClubDirectory.aspx"
    Dim http As New XMLHTTP60, html As New HTMLDocument, postdata As String
    Dim posts As Object, post As Object
    Dim items As Object, item As Object, elem As Object
    Dim req1 As String, req2 As String
   
    With http
        .Open "POST", url, 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"
        .send
        html.body.innerHTML = .responseText
    End With
   
    Set posts = html.getElementById("__VIEWSTATE")
    req1 = WorksheetFunction.EncodeURL(posts.Value)
   
    Set post = html.getElementById("__EVENTVALIDATION")
    req2 = WorksheetFunction.EncodeURL(post.Value)
   
    postdata = "ctl00%24scm=ctl00%24MainContent%24pnlFilters%7Cctl00%24MainContent%24btnSearch&__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__VIEWSTATE=" & req1 & "&__VIEWSTATEGENERATOR=FD74C8C4&__EVENTVALIDATION=" & req2 & "&ctl00%24txtSearchField=SEARCH&ctl00%24MyGolfLoginSummary%24tbMyGolfLogin_Username=&ctl00%24MyGolfLoginSummary%24tbMyGolfLogin_Password=&ctl00%24MainContent%24cbRegion=0&ctl00%24MainContent%24cbHoleOpt=0&ctl00%24MainContent%24tbSearch=SEARCH&__ASYNCPOST=true&ctl00%24MainContent%24btnSearch=GO"

    With http
        .Open "POST", url, 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"
        .send postdata
        html.body.innerHTML = .responseText
    End With

    For Each elem In html.getElementsByClassName("align-left")
        With elem.getElementsByTagName("h2")
            If .Length Then Row = Row + 1: Cells(Row, 1) = .item(0).innerText
        End With
        With elem.getElementsByTagName("p")
            If .Length Then Cells(Row, 2) = .item(0).innerText
        End With
    Next elem
End Sub