Send an array string inside POST Request VBA Excel

Question

I'm trying to send a post request to an API that takes an array of strings as an argument.

It comes out an error specifying that the types are not allowed and when the request is sent correctly all the data is left in the first position of the array (keyArray[0]).

The code I am using is the following:

Dim lastRow As Variant
lastRow = Range("B" & Rows.Count).End(xlUp).Row

Dim vvArray As Variant
vvArray = Range("B12:B" & lastRow).Value

Dim vvArrayString() As String
ReDim vvArrayString(1 To UBound(vvArray))
For i = 1 To UBound(vvArray)
    vvArrayString(i) = vvArray(i, 1)
Next

Dim TCRequestItem As Object
Set TCRequestItem = CreateObject("WinHttp.WinHttpRequest.5.1")
TCRequestItem.Open "POST", "url", False
TCRequestItem.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
TCRequestItem.send ("keyArray=" + vvArrayString)

Gives me a compilation error saying that types are not allowed, the problem is in the last line of code `TCRequestItem.send ("keyArray=" + vvArrayString)`. — dvque, Sep 24 '20 at 07:17
I've tried to do this and it works but I think there must be another way to get it: `TCRequestItem.send ("keyArray[0]=" + vvArrayString(1) + "&keyArray[1]=" + vvArrayString(2) + ...)` — dvque, Sep 24 '20 at 07:24

Spencer Barnes · Accepted Answer · 2020-09-24T08:22:18.603

I don't understand why you set vvArray and then vvArrayString? Why not go straight to vvArrayString by looping through column B?

Dim LastRow as Long 
LastRow = Range("B" & Rows.Count).End(xlUp).Row

Dim vvArrayString(1 to LastRow-11)
For i = 12 to LastRow
    vvArrayString(1-11) = Range("B" & i).text
Next

That should set the array correctly for you, you can then carry on to the next bit of code (the http request).

EDIT: the http request could also use a similar loop, as it's in such a simple repeating pattern. However you'd need a separate variable for it;

Dim strPostReq as String 'here's your separate variable

For x = 1 to LastRow-11
    'Just add the same pattern to the end of the string each time
    strPostReq = strPostReq & "keyArray[" & x-1 & "]=" & vvArrayString(x) & "&"
Next
'Then remove the last '&' from the string
strPostReq = Left(strPostReq, Len(strPostReq) - 1)

Then instead of the long previous string, you just do TCRequestItem.send(strPostReq)

For the next bit of code I've tried to do this and it works `TCRequestItem.send ("keyArray[0]=" + vvArrayString(1) + "&keyArray[1]=" + vvArrayString(2) + ...)` maybe declaring a function that returns that string can solve the problem... — dvque, Sep 24 '20 at 07:34
Looks like you could use another loop for that? Bear with, I'll edit it into my answer — Spencer Barnes, Sep 24 '20 at 08:15

Send an array string inside POST Request VBA Excel

1 Answers1