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
– dvque, Commented Sep 24, 2020 at 7: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
– dvque, Commented Sep 24, 2020 at 7:24

Spencer Barnes · Accepted Answer · 2020-09-24 08:22:18Z

1

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)

edited Sep 24, 2020 at 8:22

answered Sep 24, 2020 at 6:56

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dvque Over a year ago

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

Spencer Barnes Over a year ago

Looks like you could use another loop for that? Bear with, I'll edit it into my answer

Collectives™ on Stack Overflow

Send an array string inside POST Request VBA Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related