Unable to paste API data to excel
I can get the data from the API but I am having trouble pasting the data into Excel. My code sample is below.
When I check the responseText with msgBox, I see all the data. But when I try pasting it into Excel, it only pastes the headers for some reason.
I also tried putting the data into a variable first and then paste into Excel, but it’s the same result.
Dim iXMLHTTP As Object Set iXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP") Dim sURL As String iXMLHTTP.Open "GET", sURL, False iXMLHTTP.setRequestHeader "Content-Type", "text/xml" iXMLHTTP.send Range("A1") = iXMLHTTP.responseText
Originally asked by scott - https://codingislove.com/http-requests-excel-vba/#comment-497
I'm sure It is because of the line breaks in the response text. I looked into the API documentation here - http://www.barchartondemand.com/api/getHistory/queries
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
Let me know if that helps.