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.
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