1

I need to create a program that accesses a REST API and imports the JSON response into Excel. I am using XMLHTTP WebRequest to get the response into an string and then use the VBA-JSON Library to parse it.

Sample code:

Dim Url As String
Dim Response As String
Dim HttpReq, ResponseObj As Object
Url = "https://jsonplaceholder.typicode.com/posts"

'Download JSON response and Parse it into an object
Set HttpReq = CreateObject("WinHttp.WinHttprequest.5.1")
HttpReq.SetTimeouts -1, -1, -1, -1
HttpReq.Open "GET", Url, False
HttpReq.Send
Response = HttpReq.ResponseText        'Line with Out of Memory Error
ResponseObj = ParseJson(Response)

'This is followed by further code that places the response rows into Excel Cells

The input is well structured array, similar to the URL used here and the current code works well for majority of requests. However there are some requests where the response is approximately 40 MB where I get an "Out of Memory" error on retrieving the response (Line with reference to HttpReq.ResponseText). I have tried restarting the machine, closing other programs, Excel Safe Mode, etc but the error persists.

The question I have is, is there any way that I can download, parse and import data into Excel in a more memory efficient manner so that I do not run into the memory error above. For reference, I need to use Excel 2010 32 bit as that's the only approved and installed version at my workplace.

Improve this question
10
  • If you are in Excel 2013 or later, you might be able to load and parse the JSON file in Power Query, and then load it to a worksheet in the form of a table. Maybe ParseJson() performs string concatenation (just guessing, could be wrong) -- which VBA is typically inefficient at doing. Commented Feb 17, 2018 at 23:15
  • Could you please share the URL for such large response, for testing and reproducing the error (check MCVE). Take a look at this and this. Commented Feb 18, 2018 at 0:25
  • @chillin Do you have any examples of doing this with powerquery please? Many thanks Commented Feb 18, 2018 at 9:45
  • 1
    Saving the responses to disk as .json files would allow OP to keep static copies of the responses, meaning refreshing the query doesn't necessarily mean re-requesting the data -- which makes it less likely to run into API quota limits, at the cost of having to store the responses locally. Commented Feb 18, 2018 at 11:58
  • 1
    @QHarr, you might want to follow this link. You can grab any json file the way you want from a webpage using Power Query. Commented Feb 18, 2018 at 14:05

1 Answer 1

Reset to default
1

Try the below approach to get the Id,Title and body from that webpage.

Sub Get_data()
    Dim HTTP As New XMLHTTP60, res As Variant
    Dim r As Long, v As Long

    With HTTP
        .Open "GET", "https://jsonplaceholder.typicode.com/posts", False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        res = Split(.responseText, "id"":")
    End With

    r = UBound(res)

    For v = 1 To r
        Cells(v, 1) = Split(res(v), ",")(0)
        Cells(v, 2) = Split(Split(Split(res(v), "title"":")(1), """")(1), """,")(0)
        Cells(v, 3) = Split(Split(Split(res(v), "body"":")(1), """")(1), """,")(0)
    Next v
End Sub

Reference to add to the library:

Microsoft XML, V6.0
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I think OP is attempting to parse JSON response, not HTML. Maybe I am wrong.
Oh I see!! What does json response contain, Any data? If his ultimate goal is to reach the data, What the above solution is doing then?
My understanding is your solution expects the response to be HTML and contain ID, Title, Body, but JSON is syntactically different and consists of key-value pairs -- which wouldn't contain IDs, Title, Body.
Yeah, I know about the structural pattern of json. The thing is there is no built-in library in vba to scrape json data. As VBA-tools/VBA-JSON is an add-in, so I offered an alternative approach without using any third party tool. That's it.
Sorry, I hadn't checked the contents of "https://jsonplaceholder.typicode.com/posts" until now. Your answer makes sense, apologies.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.