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

Unable to fill in inputs to perform a web search

shahin

Active Member
Hi there! Hope you all are doing well. I got a website where I can't place my search inputs using vba to get populated data. So far when I worked with "GET" request method with inputs, I found "name" option to place accordingly in "Postdata" section (Seeing my pasted code below you will get to know what i meant by Postdata) to perform a successful search. But, this time working with some site "https://proadvisor.intuit.com/app/accountant/search?region=US" I can't find any such option I'm familiar with. If anyone helps me on this, I'll really be grateful to him. Thanks in advance.

Criteria to be fulfiled to perform a search:

1.Location ------------>Sutton-Alpine, AK
2.Industry served--------->Non Profit
3.Service Provided---------->Audits/reviews/compilations
4.Product supported--------->QuickBooks Online Edition
5.Distance within------------>25 miles

Code:
Sub WebData()
Dim http As New MSXML2.XMLHTTP60, html As New HTMLDocument
Dim PostData As String, elems As Object, elem As Object

With http
    PostData = ""
    http.Open "GET", "https://proadvisor.intuit.com/app/accountant/search?region=US" & PostData, False
    http.setRequestHeader "Content-Type", "text/xml"
    http.send
    html.body.innerHTML = .responseText
End With

Set elems = html.getElementsByClassName("accountant-name")
    For Each elem In elems
        x = x + 1
        Cells(x, 1) = elem.innerText
    Next elem
End Sub
 
This.
Code:
region=US&latitude=61.7958256&longitude=-148.8045856&location=Sutton-Alpine%2C%20AK&source=US-STANDALONE&radius=25&pageNumber=1&pageSize=10&sortBy=&industryFilter=340&serviceFilter=550,90
 
Thanks sir for teaching me how to find "query string parameters". There are a lot to learn about chrome developer tool. I haven't thought of it much earlier, though. Btw, I'm doing something wrong for sure that is because can't get any data. Expecting your magnificent touch on this If you have time to spare. Thanks.
Code:
Sub WebData()
Dim http As New MSXML2.XMLHTTP60, html As New HTMLDocument
Dim PostData As String, elems As Object, elem As Object

PostData = "region=US&latitude=61.7958256&longitude=-148.8045856&location=Sutton-Alpine%2C%20AK&source=US-STANDALONE&radius=25&pageNumber=1&pageSize=10&sortBy=&industryFilter=340&serviceFilter=550,90"
With http
    .Open "GET", "https://proadvisorservice.intuit.com/v1/search?" & PostData, False
    .setRequestHeader "Content-Type", "text/xml"
    .send
    html.body.innerHTML = .responseText
End With

Set elems = html.getElementsByClassName("accountant-name")
    For Each elem In elems
        x = x + 1
        Cells(x, 1) = elem.getElementsByTagName("a")(0).href
    Next elem
End Sub
 
Last edited:
Yes, it's in JSON format.

Also, you need to set couple of headers since it's using versioning info.

Without proper header you will get...
Code:
{
  "timestamp" : 1492459117424,
  "status" : 400,
  "errors" : [ {
    "message" : "Version specified in request is not supported by this resource.",
    "codes" : [ "400.no.such.version" ]
  } ]
}

By setting proper header like below.
Code:
    http.setRequestHeader "Content-Type", "application/json; charset=utf-8"
    http.setRequestHeader "Accept", "application/json;version=1.1.0"

You will get actual JSON response.
 
Thanks a zillion sir Chihiro. I'm very unhappy that I couldn't press the like button thousand times.
 
One more thing--should I always set request headers as the way you did here in case of json response? Thanks again.
 
Depends on site. Web scraping is something that you can't have single code that fits all sites. You need to first study the site using developer tool and other tools to understand it's behaviour/requirement and then customize your code to conform to it.
 
Found a solution to scrape the json data. First up I installed the json converter from this link "https://github.com/VBA-tools/VBA-JSON" then added the "Microsoft scripting runtime" library to the vba editor and then executed the below code. Now, it works like magic.
Code:
Sub JsonData()
Dim http As New MSXML2.XMLHTTP60
Dim PostData As String, JSON As Object
Dim results As VBA.Collection
Dim result As Scripting.Dictionary

PostData = "region=US&latitude=61.7958256&longitude=-148.8045856&location=Sutton-Alpine%2C%20AK&source=US-STANDALONE&radius=25&pageNumber=1&pageSize=10&sortBy=&industryFilter=340&serviceFilter=550,90"

With http
    .Open "GET", "https://proadvisorservice.intuit.com/v1/search?" & PostData, False
    .setRequestHeader "Content-Type", "application/json; charset=utf-8"
    .setRequestHeader "Accept", "application/json;version=1.1.0"
    .send
    Set JSON = JsonConverter.ParseJson(.responseText)
End With
MsgBox http.responseText
Set results = JSON("searchResults")
For Each result In results
    i = i + 1
    Cells(i, 1).Value = result("firstName")
    Cells(i, 2).Value = result("lastName")
    Cells(i, 3).Value = result("city")
Next result
Set JSON = Nothing: Set results = Nothing: Set result = Nothing
End Sub
 
Last edited:
Hi !

As you can find within some threads of this forum
you ever do not need any JSon converter
just using ScriptControl ActiveX or, as JSon structure is nothing more
than text, just using inner VBA text functions like Split
 
IMHO. Unless you are sure that returned JSON is "safe" it's best to use string manipulation or other methods (other than ScriptControl).
 
Is it possible to do string manipulation on json response? If so, then hopefully somebody will provide me with a link following which i can get to any demo on that.
 
Without using third party libs and again using string manipulation I've got it sorted:
Code:
Sub JsonData()
Dim http As New XMLHTTP60
Dim PostData As String, itm As Variant

PostData = "region=US&latitude=61.7958256&longitude=-148.8045856&location=Sutton-Alpine%2C%20AK&source=US-STANDALONE&radius=25&pageNumber=1&pageSize=10&sortBy=&industryFilter=340&serviceFilter=550,90"

With http
    .Open "GET", "https://proadvisorservice.intuit.com/v1/search?" & PostData, False
    .setRequestHeader "Content-Type", "application/json; charset=utf-8"
    .setRequestHeader "Accept", "application/json;version=1.1.0"
    .send
    itm = Split(.responseText, "proAdvisorID"" : ")
End With
x = UBound(itm)
For y = 1 To x
    Cells(y, 1) = Split(Split(itm(y), "firstName"" : """)(1), """")(0)
    Cells(y, 2) = Split(Split(itm(y), "lastName"" : """)(1), """")(0)
    Cells(y, 3) = Split(Split(itm(y), "city"" : """)(1), """")(0)
    Cells(y, 4) = Split(Split(itm(y), "state"" : """)(1), """")(0)
    Cells(y, 5) = Split(Split(itm(y), "zip"" : """)(1), """")(0)
    Cells(y, 6) = Split(Split(itm(y), "phoneNumber"" : """)(1), """")(0)
Next y
End Sub
 
Back
Top