Thanks a ton. The great thing about creating any thread in the forum of your blog is that you never disappoint anyone from your support. Btw, according to the link you have provided above, if we get this feature [Try/Catch/Finally ] in vba then it will be awesome.
Finally, found the solution. I am pasting the full code below for those who do stumble across such problem as i had to face earlier:
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"
.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"
Set JSON = JsonConverter.ParseJson(.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")
Set JSON = Nothing: Set results = Nothing: Set result = Nothing
Const URL = "https://newyork.craigslist.org/search/ata"
Const pref = " https://images.craigslist.org/"
Const suff = "_300x300.jpg"
Dim html As New HTMLDocument
Dim topics As Object, post As Object
With New MSXML2.XMLHTTP60
.Open "GET", URL, False
html.body.innerHTML = .responseText
Set topics = html.getElementsByClassName("result-image gallery")
On Error Resume Next
For Each post In topics
x = x + 1
Cells(x, 1) = pref & Split(Split(post.getAttribute("data-ids"), ":")(1), ",")(0) & suff
Set html = Nothing: Set topics = Nothing
You are looking at a page which fetches XML from some API, renders XML and formats it using CSS. The best solution is to find the source URL of XML file by inspecting XHR requests of that page. Once you find the source, Make a simple GET request and grab the XML.
Hello @Axim, Handling OAuth in VBA is a complicated process. Also, Bexio API doesn't offer any service accounts for authentication so manual login will be required.
The best way to handle your use case in VBA would be -
Manually get an access token from browser.
Then make POST requests to API using your access token.
Access token is valid only for 1 hour so you have to refresh the token manually.
This is not a simple process if you are new to coding. You should probably hire a freelancer to get this done. If you are hiring a freelancer then go for a better solution to handle authentication process using a web service.
I got you Ranjith, but still some confusions are there. I understood that if for loop continues and i keep my html variable same for all then at some point in the loop under any condition it may get altered and can't access its main links by which it is extracting information. But what i can't understand is that why the last two "htmldocument" variables are same because they are in loop also and you know in this subroutine three for loops are running. Anyways, if i make the last two "htmldocument" variable different from each other then it comes up with messy results and program crashes which i don't want. And, this is where another confusion begins. Thanks you respond and hope to have another. Have a nice time.