ARRAY IN JSON VBA

[d4ta4nalyst]

hello, my knowledge as a developer is not that good, but by collecting code on the web, I was able to adapt the content to what I really wanted.

The question is that I am specifically working with a remote version of Limesurvey (a survey software). But apparently, the software, to make remote requests uses json. The case is that I try to show certain attributes, which are an array and I don't know how to proceed to do this adapted to VBA...

If someone has some idea and can tell me how, even if it's the introduction to learn how to do this, since I've really been several weeks and I can't get this...

I show you the code in json:

{
"method":"export_responses",
"params": [
"U053k2PTgDSifiZKkfZUQ5v6wlrbfsnt",
"433682",
"csv",
null,
"complete",
null,
null,
"1",
"30",
["firstname", "lastname"]
],
"id":1
}

I show you the code in VBA-JSON:

Sub export_limesurvey()
Dim key As String
Dim limeuser As String, limepass As String, limeurl As String, URL As String
Dim jsonText As String, jsonObject As Object
Dim SurveyID As String, DocumentType As String, LanguageCode As String, CompletionStatus As String, HeadingType As String, ResponseType As String, FromResponseID As String, ToResponseID As String
Dim Fields As VBA.Collection
Dim export64 As String, export64Decoded As String

limeurl = "URL CENSORED"
limeuser = "CENSORED"
limepass = "CENSORED"
SurveyID = Worksheets("Hoja2").Range("A6").Value 'Valor entre comillas para indicar ID encuesta a exportar (He hecho referencia a celda)
DocumentType = "csv" 'Tipo de formato en el que se desea exportrar (pdf,csv,xls,doc,json)
LanguageCode = "es" 'OPCIONAL: indicar idioma de exportación (solo si la encuesta está en ese idioma)
CompletionStatus = "complete" 'Exportar encuestas completas, incompletas o todas (complete,incomplete,all)
HeadingType = "full" 'Impresión de los titulos de las encuestas; códigos, todo o abreviados (code,full,abbreviated)
ResponseType = "long" 'Mostrar los valores de las preguntas de selección; corto para código y largo para mostrar valores (short,long)
FromResponseID = "0" 'Indicar desde el primer número de respuesta en la encuesta desde el que se desea exportar
ToResponseID = "10" 'Indicar hasta el último número de respuesta en la encuesta hasta el que se desea exportar
Fields = Array("firstname", "lastname")

'Inicialización
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = limeurl + "/admin/remotecontrol"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/json"

'Establecer acceso (get_session_key)
sendtext = "{""method"":""get_session_key"",""params"": [""" + limeuser + """,""" + limepass + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
key = jsonObject("result")

Where I think the error is here. Say the code works without adding the "fields" array:

'Exportar respuestas (export_responses)
sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """,""" + LanguageCode + """,""" + CompletionStatus + """,""" + HeadingType + """,""" + ResponseType + """,""" + FromResponseID + """,""" + ToResponseID + """,""" + [""" + Fields + """] + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
export64 = jsonObject("result")

Thanks for reading...

[Nick-vanGemeren]

As you don't give any code about handling the export_responses response, I assume that your problem is in constructing the request string. You also have not explained what the 'error' is.

As defined, Fields is a Collection. You cannot assign an Array to it (even with Set). But in fact you need the field names as a string to put in sendtext. This would be sufficient.

    Dim Fields As String
    Fields = Join(Array("""firstname""", """lastname"""),",")

All those quotes make the code less readable and harder to debug. Let's define some simple functions to wrap a string in quotes or make other bits of JSON strings

' Wrap - Wrap string with some character(s)
Function Wrap(wstart As String, wfinal As String, str) As String
    Wrap = wstart & str & wfinal
  End Function

' WQ - Wrap string with double-quote
Function WQ(str) As String
    WQ = Wrap(Chr(34), Chr(34), str)
  End Function

' CSL - make a comma separated list
' Call with either list of strings or one other function's ParamArray
Function CSL(ParamArray listbits()) As String
    If VarType(listbits(0)) >= vbArray Then
        CSL = Join(listbits(0), ",")
      Else
        CSL = Join(listbits, ",")
      End If
  End Function

' Jarray - Make a JSON array (Wrap values with square brackets)
Function Jarray(ParamArray values()) As String
    Jarray = Wrap("[", "]", CSL(values))
  End Function

' KP - make a key pair with quoted key
Function KP(skey As String, svalue As String) As String
    KP = WQ(skey) & ":" & svalue
  End Function

' KPgroup - Make a key pair group (Wrap pairs with braces)
Function KPgroup(ParamArray pairs()) As String
    KPgroup = Wrap("{", "}", CSL(pairs))
  End Function

Then you can use:

    Fields = Jarray (WQ("firstname"), WQ("lastname"))

    sendtext = KPgroup( _
        KP("method", WQ("get_session_key")), _
        KP("params", Jarray(WQ(limeuser), WQ(limepass))), _
        KP("id", 1) _
      )
    
    Dim parms As String
    parms = Jarray( _
        WQ(Key), _
        WQ(SurveyID), _
        WQ(DocumentType), _
        WQ(LanguageCode), _
        WQ(CompletionStatus), _
        WQ(HeadingType), _
        WQ(ResponseType), _
        WQ(FromResponseID), _
        WQ(ToResponseID), _
        Fields _
      )
    sendtext = KPgroup( _
        KP("method", WQ("export_responses")), _
        KP("params", parms), _
        KP("id", 1) _
      )

Several key values contain numbers, but are being passed as quoted strings. Check whether the receiver accepts that.

Web access:

• You have to Open the objHTTP connection again before the second send.
• After both send operations, you should check the response status.
• Doing the access in a function will make the code clearer.
• For complex access, consider using VBA-Web.

If this solves your problem(s), please close your issue here.

[Nick-vanGemeren]

D'oh! I missed the more obvious method of creating a data structure and converting it to JSON.

    Dim Jdict As Dictionary

    Set Jdict = New Dictionary
    With Jdict
        .Add "method", "get_session_key"
        .Add "params", Array(limeuser, limepass)
        .Add "id", 1
      End With
    sendtext = JsonConverter.ConvertToJson(Jdict)
    Set Jdict = Nothing
  
    Set Jdict = New Dictionary
    With Jdict
        .Add "method", "export_responses"
        .Add "params", Array( _
            Key, _
            SurveyID, _
            DocumentType, _
            LanguageCode, _
            CompletionStatus, _
            HeadingType, _
            ResponseType, _
            FromResponseID, _
            ToResponseID, _
            Array("firstname", "lastname") _
          )
        .Add "id", 1
      End With
    sendtext = JsonConverter.ConvertToJson(Jdict)
    Set Jdict = Nothing

[d4ta4nalyst]

D'oh! Me perdí el método más obvio de crear una estructura de datos y convertirla a JSON.

    Dim Jdict As Dictionary

    Set Jdict = New Dictionary
    With Jdict
        .Add "method", "get_session_key"
        .Add "params", Array(limeuser, limepass)
        .Add "id", 1
      End With
    sendtext = JsonConverter.ConvertToJson(Jdict)
    Set Jdict = Nothing
  
    Set Jdict = New Dictionary
    With Jdict
        .Add "method", "export_responses"
        .Add "params", Array( _
            Key, _
            SurveyID, _
            DocumentType, _
            LanguageCode, _
            CompletionStatus, _
            HeadingType, _
            ResponseType, _
            FromResponseID, _
            ToResponseID, _
            Array("firstname", "lastname") _
          )
        .Add "id", 1
      End With
    sendtext = JsonConverter.ConvertToJson(Jdict)
    Set Jdict = Nothing

Hello Nick, first of all, thanks for answering and taking the time to raise the solutions you propose, it's a real pleasure!

I try to propose what you propose, but it doesn't work for me... The idea is to show the data collected in a table. Perhaps I wasn't clear at the time of proposing the exercise, I also didn't put all the code, I'm going to post it below, see if you see the error. Which by the way, the error is when trying to show the array, by some chance that I don't know (I think it's in the way I call the parameters):

My code VBA:

Sub export_limesurvey()
Dim key As String
Dim limeuser As String, limepass As String, limeurl As String, URL As String
Dim jsonText As String, jsonObject As Object
Dim SurveyID As String, DocumentType As String, LanguageCode As String, CompletionStatus As String, HeadingType As String, ResponseType As String, FromResponseID As String, ToResponseID As String
Dim Fields As String
Dim export64 As String, export64Decoded As String

limeurl = "URL CENSORED"
limeuser = "CENSORED"
limepass = "CENSORED"
SurveyID = Worksheets("Hoja2").Range("A6").Value 'Valor entre comillas para indicar ID encuesta a exportar (He hecho referencia a celda)
DocumentType = "csv" 'Tipo de formato en el que se desea exportrar (pdf,csv,xls,doc,json)
LanguageCode = "es" 'OPCIONAL: indicar idioma de exportación (solo si la encuesta está en ese idioma)
CompletionStatus = "complete" 'Exportar encuestas completas, incompletas o todas (complete,incomplete,all)
HeadingType = "full" 'Impresión de los titulos de las encuestas; códigos, todo o abreviados (code,full,abbreviated)
ResponseType = "long" 'Mostrar los valores de las preguntas de selección; corto para código y largo para mostrar valores (short,long)
FromResponseID = "0" 'Indicar desde el primer número de respuesta en la encuesta desde el que se desea exportar
ToResponseID = "10" 'Indicar hasta el último número de respuesta en la encuesta hasta el que se desea exportar
Fields = Join(Array("""firstname""", """lastname"""), ",")

'Limpiar página excel
Cells.Clear

'Inicialización
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = limeurl + "/admin/remotecontrol"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/json"

'Establecer acceso (get_session_key)
sendtext = "{""method"":""get_session_key"",""params"": [""" + limeuser + """,""" + limepass + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
key = jsonObject("result")

'Exportar respuestas (export_responses)
sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """,""" + LanguageCode + """,""" + CompletionStatus + """,""" + HeadingType + """,""" + ResponseType + """,""" + FromResponseID + """,""" + ToResponseID + """,""" + [""" + Fields + """] + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
export64 = jsonObject("result")

'Codificar respuestas exportadas
export64Decoded = DecodeBase64(export64)

'Cerrar sesión
sendtext = "{""method"":""release_session_key"",""params"": [""" + key + """],""id"": 1}"
objHTTP.Send (sendtext)

'Divide las respuesta en varias líneas, de lo contrario, todo está en una celda
s = export64Decoded
i = 0
While Split(s, Chr(13) + Chr(10))(i) <> ""
Cells(i + 1, 1) = Split(s, Chr(13) + Chr(10))(i)
i = i + 1
Wend

'Conversión a CSV
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True

Cells.WrapText = False

End Sub

This code currently doesn't work, because I have (badly) added the array to display first and last names. In the "export_responses" area, however, the code that I show below, without the array, does work correctly (I shorten the code to the "export_responses" section, since that is the only thing that changes:

'Exportar respuestas (export_responses)
sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """,""" + LanguageCode + """,""" + CompletionStatus + """,""" + HeadingType + """,""" + ResponseType + """,""" + FromResponseID + """,""" + ToResponseID + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
export64 = jsonObject("result")

The truth is that it is making it very difficult for me to solve this... I don't know what is really wrong, when I try to display the array, I get an error in Excel, some subscript... the only thing I want to achieve is to do is the following json code to excel:

KEY:

{
"method": "get_session_key",
"params":["USER","PASS!"],
"id":1
}

EXPORT_RESPONSES:

{
"method":"export_responses",
"params": [
"U053k2PTgDSifiZKkfZUQ5v6wlrbfsnt",
"433682",
"csv",
null,
"complete",
null,
null,
"1",
"30",
["firstname", "lastname"]
],
"id":1
}

thanks again nick, any answer is welcome, let's see if we can provide a solution to this since there is nothing on the net, and I think that more than one could be of help

[Nick-vanGemeren]

You are making it difficult for yourself by being over-confident in your code. Try stepping through the code with the debugger and checking variables have expected values. You should then at least be able to say which code statement causes your vague 'some subscript...' error.

Given my remarks on web access at the end of my first reply, I need you to prove to me and yourself that the server response to the export_responses request is reasonable, preferably by dumpingjsonTextto a file and attaching to a reply. Do not paste it into a comment. If you don't want to attach, you could give a temporary Dropbox link ...

I think the first time you use a subscript is in While Split(s, Chr(13) + Chr(10))(i) <> "". In general, there is no guarantee that the last element of the array fromSplitwill be zero length. So the loop can easily run off the end of the array, giving as 'subscript out of range' error. Recode the section as

    Dim lines()
    lines = Split(export64Decoded, vbCrLf)
    i = UBound(lines) + 1
    If i = 0 Then
        MsgBox "No survey lines received", vbCritical
        Exit Sub
      End If
    Range("A1", Cells(i, 1)).Value = Application.WorksheetFunction.Transpose(lines)

If the worksheet is not empty beforehand, you may want to include aCells.Clearor similar to remove old data.

This is not (yet) a difficult problem. I can easily give you a module with my recommended code. But using your own code and learning VBA/Excel is very good as well.

It's not necessary to include my previous post in your reply, although it does show that some automatic translation is happening. Hopefully that will not distort the meaning too much.

[d4ta4nalyst]

Ok, I'll show you how it works in json with screenshots and the full code (I'll censor the username/password parts, but not the session key part because by the time I upload this, it will be expired I guess)

Then the JSON result (the key) is needed to export the responses:

The result of this is shown encoded in base64, to see it clearly, it would be enough to decode it, I can do this easily in Excel with a function in VBA, it works correctly

To understand the parts that I indicate in JSON in the previous screenshot, I indicate what each thing is in order:

1. session key
2. survey id
3. type of format I want to export
4. language (null in case of all languages)
5. response status (complete for complete responses only)
6. It is used to indicate how I want to show the title of the questions (I put null, because I am not interested at the moment)
7. It is used to show if I want long or short answers (null because I don't want at the moment)
8. From the answer (1 as the first answer)
9. Until the answer (30 to indicate that we want until that)

The official documentation of the server and how the data is treated, you can see it here:

https://api.limesurvey.org/classes/remotecontrol_handle.html#method_export_responses

I decode the result in excel so you can see what exactly we get:

I managed to transfer this to Excel with VBA/JSON. I tried to create a Dropbox account to be able to transfer the file temporarily, but apparently this is paid... so, I'm going to try uploading it to Google Drive. The file contains my password and username + test URL, but it doesn't really matter, it's a test server I have and the information is not sensitive. When you tell me that you have tried it, I can change the password, it doesn't matter. I also attached screenshots so you can see that it works:

file: https://drive.google.com/file/d/1iAoYZywVlXwMBkBP-_62NYhhy59MiRWh/view?usp=sharing

So what is the problem? if everything seems to be done... well, the fact is that if I want to show the name and surname of the participants, in the json, I can indicate this "array":

If you see it, the result is shortened compared to the previous capture. This is because by placing "first name" and "last name", it only displays this information. I am going to copy the encoded result and pass it to excel, so that you can see the result:

So, the problem I have is that I can't understand how to add the first and last name part to my VBA/JSON code, I practically have everything done, except this part, I find it so difficult for me to understand how to do this. I don't think I understand very well how to make a JSON array in VBA... if you can help me, I would really appreciate it... I accept that you can send me modules if you want with another much better way of doing the code...

feel free to use my code, let's see if you can do the array part... thanks for reading me again and spending some of your time on this

[Nick-vanGemeren]

Fine. I have your XLSM. I will respond later.

[d4ta4nalyst]

Thanks you very much for your time Nick, I am looking forward to your response!

[Nick-vanGemeren]

You already understand that the aFields positional parameter is an array of 'selected database field' names. If you specify the array, you get those fields, probably in the same order. If the other parameters cannot be set to give many fields by default (in which case you can delete the excess columns), you need to decide which fields you want and then find their precise database names. The database administrator should be able to give the names.

You may also be able to get extra data using the list_participants and/or list_questions methods.

So which fields do you want?

The test data on Hoja1 looks very strange to me. Is it what you were expecting??

Was the data on Hoja1 produced by the export_limesurvey script? If not, what does it produce?

[d4ta4nalyst]

Translated into JSON, this is basically what I have in the attached excel file:

And this is what I want, the array with the "firstname" and "lastname" fields. Would you know how to do it applied in my code?

[Nick-vanGemeren]

I gave the code to generate that request in my second reply. I recommend not to use null values; it is clearer if you put the default option explicitly.

The 2-field request that you are asking for will not produce an Excel sheet with 9 columns.

Let's rephrase the question. What should appear as column headings on row 1 of Hoja1?

[d4ta4nalyst]

Well, I don't know how to implement your code in mine... I'm pretty new to it. Could you attach a module to see if I can run it?

Yes, I know, on the one hand I have the solution for the entire survey (the 9 columns), but on the other hand, what I only want to get is the firstname and the lastname, nothing more

The answer to your sentence is "firstname", that should appear in the title of column 1, row 1

[Nick-vanGemeren]

OK. You have done a good job putting together that complex XLSM with limited VBA coding/debugging skills. We also may have had some communication issues.

The code you posted before, summarised as:

Dim Fields As String
...
Fields = Join(Array("""firstname""", """lastname"""), ",")
...
sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """,""" + LanguageCode + """,""" + CompletionStatus + """,""" + HeadingType + """,""" + ResponseType + """,""" + FromResponseID + """,""" + ToResponseID + """,""" + [""" + Fields + """] + """],""id"": 1}"

... needs the final line changing to

sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """,""" + LanguageCode + """,""" + CompletionStatus + """,""" + HeadingType + """,""" + ResponseType + """,""" + FromResponseID + """,""" + ToResponseID + """,[" + Fields + "],""id"": 1}"

... which generates the reuired JSON request string:
{"method":"export_responses","params": ["","433682","csv","es","complete","full","long","0","10",["firstname","lastname"],"id": 1} }

I will have a draft XLSM with revised/new coding ready in a day or two.

[d4ta4nalyst]

Hello again, I try to put what you are saying in my code and I get the following error:

The error says "invalid use of null", but I don't have anything null declared, so I don't know what that refers to.

The "export_responses" part of the code was like this:

sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """,""" + LanguageCode + """,""" + CompletionStatus + """,""" + HeadingType + """,""" + ResponseType + """,""" + FromResponseID + """,""" + ToResponseID + """,[" + Fields + "],""id"": 1}" objHTTP.Send (sendtext) jsonText = objHTTP.responseText Set jsonObject = JsonConverter.ParseJson(jsonText) export64 = jsonObject("result")

Also, I have seen, that in your code, a "]" is missing at the end of the code, I don't know if it should be declared like this in VBA/JSON. But if you look at the json screenshots I put before, it does have a "]" at the end as well, to close the "params". I don't know if this has something to do with the null error

[Nick-vanGemeren]

The closing bracket forparams is at the syart of"],""id"": 1}".
Set a breakpoint where you think the error is occurring. Run (press F5 or Mac equivalent) to that point and either hover over the variables in the Locals window.

Macros executing in Sheet code apparently do not offer Debug as an option after errors. My version has very little Sheet code and nearly all Module code.

[Nick-vanGemeren]

Hi Jesús. My XLSM for responses and respondents is available. The release notes will evolve during tomorrow.
I doubt that there will be anything more of interest to VBA-JSON users. So I suggest that you close the issues (comments will still be possible) and that we continue the conversation by email.

[Nick-vanGemeren]

Did you manage to download the files?
You do not need to sign in to Dropbox or Google. Just use the Download button.

[d4ta4nalyst]

Hi Nick, sorry for the delay in responding;

I've gotten to see his work, by the way; great job Nick, congratulations, his work looks really good!

I seem to remember that the only thing that didn't work so well was when exporting all the participants, but I'll keep reviewing your work because I really like it.

For my part, I managed to do the job by extracting raw code from json, thanks to this I used Power Query, which when it comes to displaying the data in Excel, looks quite good.

Likewise, thanks for all your work, being there and answering my questions. Thanks Nick!

[Nick-vanGemeren]

OK. That sounds as if you have solved the problems. Please close your issue here.

The latest XLSM version was uploaded on 7 December. Save that soon since it will not stay indefinitely in Dropbox.