CODEWITHSUNDEEP
jsonvbaexcel
RIBH
RIBH

Reputation: 386

Handling large JSON web response in VBA

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.

Upvotes: 1

Views: 2289

Answers (1)

SIM
SIM

Reputation: 22440

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

Upvotes: 1

Related Questions