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 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.
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.
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.
@bskbal Hi, Here's the code to make it work for this data.
Public Sub importNBAData()
Dim http As Object, JSON As Object, i As Integer, headers As Object, rowSet As Object
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://stats.nba.com/stats/leaguedashplayerstats?DateFrom=&DateTo=&GameScope=&GameSegment=&LastNGames=15&LeagueID=00&Location=&MeasureType=Advanced&Month=0&OpponentTeamID=0&Outcome=&PaceAdjust=N&PerMode=Totals&Period=0&PlayerExperience=&PlayerPosition=&PlusMinus=N&Rank=N&Season=2015-16&SeasonSegment=&SeasonType=Regular+Season&StarterBench=&VsConference=&VsDivision=", False
Set JSON = ParseJson(http.responseText)
Set headers = JSON("resultSets")(1)("headers")
Set rowSet = JSON("resultSets")(1)("rowSet")
j = 1
For Each Header In headers
Sheets(1).Cells(1, j).Value = Header
j = j + 1
i = 2
For Each Item In rowSet
j = 1
For Each detail In Item
Sheets(1).Cells(i, j).Value = detail
j = j + 1
i = i + 1
This code will import the entire data into sheet 1 along with headers. Please keep in mind that same code cannot be used for every data set. It has to be changed accordingly.
I tried copying the sample response from the web page to see if it can be printed to cell or not and it was working fine. So I can't debug without getting the actual response from API using the API key. If you can share API key in private then I can try debugging it.
If you look at the documentation link - They also support XML and JSON. You just have to change getHistory.csv in your URL to getHistory.xml or getHistory.json