Nick-vanGemeren
commented
1 year ago 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 FunctionThen 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.
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...