title: Handling large JSON web response in VBA
body: <p>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 <a href="https://github.com/VBA-tools/VBA-JSON" rel="nofollow noreferrer">VBA-JSON</a> Library to parse it.</p>
  
  <p>Sample code:</p>
  
  <pre><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
  </code></pre>
  
  <p>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 <code>HttpReq.ResponseText</code>). I have tried restarting the machine, closing other programs, Excel Safe Mode, etc but the error persists.</p>
  
  <p>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.</p>