Table from HTTP responseText VBA Excel

Question

I am working with VBA and trying to create a table from a response test using HTTP request. Here is my code:

Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", "https://example.url.com/data", False
        .send
    End With

If one navigated to the URL, the only item on the page is a CSV response that looks like this:

name,ID,job,sector johndoe,1234,creator,sector1 janedoe,5678,worker,sector2

This translates to a table with 4 columns named "name", "ID", "job", and "sector". I am pretty new to VBA and I am struggling to understand how to translate the response text into a table. But I need to get this into tabular form so I can work with the column variables. I can get the response text into a single cell:

Sheets("Sheet1").Range("A1").Value = hReq.responseText

However, I can't get the table into tabular format so I can begin working with it as I would a table. It would be great to get the data into an array in memory so that I could manipulate and analyze it using VBA, but for troubleshooting purposes, it would also be helpful to get it into an Excel Worksheet, so I can double-check my programming.

Answer 1

This loops through your header request and posts to your preferred sheet:

Sub test()
    Dim RespArray() As String
    Dim RespArray2() As String
    Dim i, i2 As Long

    Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", "https://example.url.com/data", False
        .send
    End With

    ' split the data into larger pieces
    RespArray() = Split(hReq.responseText, " ")

    ' loop through first array to break it down line by line
    For i = LBound(RespArray) To UBound(RespArray)
        ' split each line into individual pieces
        RespArray2() = Split(RespArray(i), ",")
        ' loop through second array and add to Sheet1
        For i2 = LBound(RespArray2) To UBound(RespArray2)
          Worksheets("Sheet1").Cells(i + 1, i2 + 1).Value = RespArray2(i2)
        Next i2
    Next i
End Sub

Results in