- Posts: 3
- Thank you received: 0
LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
- jnieto-test
-
Topic Author
- Offline
- New Member
-
Less
More
3 months 2 weeks ago #232791
by jnieto-test
LIST_PARTICIPANTS IN EXCEL WITH VBA JSON was created by jnieto-test
Hello, recently I use a code that I found in the community of this forum to be able to apply JSON-RCP to Excel using VBA. What this code does is download responses from Lime Survey using the remote
The issue is that when exporting responses, I do not receive the emails from the participants. I have seen that there is an option for the latter but I don't know how to apply it to VBA... can someone who understands how guide me? Thanks in advance
The issue is that when exporting responses, I do not receive the emails from the participants. I have seen that there is an option for the latter but I don't know how to apply it to VBA... can someone who understands how guide me? Thanks in advance
Please Log in to join the conversation.
3 months 2 weeks ago - 3 months 2 weeks ago #232793
by Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Replied by Joffm on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
Hi,
why didn't you answer the initial questions?
But you may read this
forums.limesurvey.org/forum/design-issue...he-user-email#230812
It explains that with the setting "all" the email is not exported, but with the individual fields it is.
Joffm
But one thing is confusing.
Your topc is about "list participants", but then you talk about "exporting responses".
So, what do you want to export?
why didn't you answer the initial questions?
It would be great if you showed us which code you are talking about.recently I use a code that I found in the community of this forum
But you may read this
forums.limesurvey.org/forum/design-issue...he-user-email#230812
It explains that with the setting "all" the email is not exported, but with the individual fields it is.
Joffm
But one thing is confusing.
Your topc is about "list participants", but then you talk about "exporting responses".
So, what do you want to export?
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Last edit: 3 months 2 weeks ago by Joffm.
Please Log in to join the conversation.
- jnieto-test
-
Topic Author
- Offline
- New Member
-
Less
More
- Posts: 3
- Thank you received: 0
3 months 2 weeks ago #232795
by jnieto-test
Replied by jnieto-test on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
Oh, sorry for not answering the main questions. I did not know very well that she had to answer them.
Yes, I copy the code below:
I'll tell you what I'm really looking for...
I know that in LimeSurvey graphic mode I can export answers with the mail of the participants. I would like to be able to do this through the remote system... but when I use this code it does not export the mail information. Doing some research I read in this forum that you could do this from the mail with an option to list the participants. So I guess it would be a good idea to combine "export responses" with "list participants" to get what I want. Although I don't know if it is possible to do this with a single option as I do in graphical mode in lime
I'm a bit of a newbie at this, thanks for replying.
Yes, I copy the code below:
' -----------------------------------------------------------------------
' ESTABLECER CONEXIÓN Y DESCARGAR ENCUESTA ESPECÍFICA
' -----------------------------------------------------------------------
' Con el siguiente código podemos establecer acceso por JSON-RCP a Lime Survey
' y utilizar códificador en Base64 para poder tratarlo con VBA para EXCEL
'
' SE NECESITA INTRODUCIR ID ENCUESTA DE FORMA MANUAL EN EL CÓDIGO
'
' Se utiliza diferentes módulos:
' VBA JSON tools: https://github.com/VBA-tools/VBA-JSON
' Base64 Deconding: http://www.vbforums.com/showthread.php?379072-VB-Fast-Base64-Encoding-and-Decoding&p=2539878&viewfull=1#post2539878
'
' Para más documentación:
' https://www.limesurvey.org/community/forums/can-i-do-this-with-limesurvey/114846-export-answers-to-excel-in-semi-realtime
'
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
Dim export64 As String, export64Decoded As String
limeurl = "XXXXXX"
limeuser = "XXXXXXX"
limepass = "XXXXX"
SurveyID = Worksheets("Hoja2").Range("A6").Value
DocumentType = "csv"
'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 + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
export64 = jsonObject("result")
'Codificar respuestas exportadas
export64Decoded = Decode64(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
'SI OCURRE ALGUN CAMBIO LLAMA LA FUNCIÓN...
'Private Sub Worksheet_Change(ByVal Target As Range)
'If Not Intersect(Target, Range("A1:Z10000")) Is Nothing Then
'Call copiar_celda
'End If
'End Sub
I'll tell you what I'm really looking for...
I know that in LimeSurvey graphic mode I can export answers with the mail of the participants. I would like to be able to do this through the remote system... but when I use this code it does not export the mail information. Doing some research I read in this forum that you could do this from the mail with an option to list the participants. So I guess it would be a good idea to combine "export responses" with "list participants" to get what I want. Although I don't know if it is possible to do this with a single option as I do in graphical mode in lime
I'm a bit of a newbie at this, thanks for replying.
Please Log in to join the conversation.
3 months 2 weeks ago #232797
by Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Replied by Joffm on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
Did you read the documentation of the API?
Here you find in "export responses"
You use only the basic call with default settings after the document type.
But here is a call with specified fields to export (you have to use the SGQA code of fields)
These two examples of a call in php.
But you see the important things.
Maybe this helps.
Joffm
Here you find in "export responses"
export_responses(string $sSessionKey,integer $iSurveyID,string $sDocumentType,string $sLanguageCode = null,string $sCompletionStatus = 'all',string $sHeadingType = 'code',string $sResponseType = 'short',integer $iFromResponseID = null,integer $iToResponseID = null,array $aFields = null): array|string
You use only the basic call with default settings after the document type.
$response = $myJSONRPCClient->export_responses(
$sessionKey,
$survey_id,
'json', // Document type : pdf,csv,xls,doc,json
null, // Language code : null : default from survey
'all', // Stautus complete|incomplete|all
null, // Heading : code|full|abbreviated : question text, default code
null // answer : short|long , default : long
);
But here is a call with specified fields to export (you have to use the SGQA code of fields)
$surveyResponses = $myJSONRPCClient->export_responses(
$sessionKey,
$survey_id,
'json', // Document type : pdf,csv,xls,doc,json
null, // Language code : null : default from survey
'complete', // Stautus complete|incomplete|all
NULL, // Heading : code|full|abbreviated : question text, default code
NULL, // answer : short|long , default : long
1, // From Response ID
3000, // To Response ID
['id','token','submitdate','lastpage','interviewtime','email','159677X490time','159677X487time','159677X490X9219SQ001','159677X490X9221SQ001']
);
These two examples of a call in php.
But you see the important things.
Maybe this helps.
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Please Log in to join the conversation.
- jnieto-test
-
Topic Author
- Offline
- New Member
-
Less
More
- Posts: 3
- Thank you received: 0
3 months 1 week ago #232902
by jnieto-test
Replied by jnieto-test on topic LIST_PARTICIPANTS IN EXCEL WITH VBA JSON
Sorry for the delay in responding.
I have seen what you tell me, and in your example... I see it clearly. I can understand what each thing is and what it is for. But it is very difficult for me to implement it in my VBA code, it is an example that I collected from the community of this forum
Next I show how my code is, would you know how to implement more parameters?
I have seen what you tell me, and in your example... I see it clearly. I can understand what each thing is and what it is for. But it is very difficult for me to implement it in my VBA code, it is an example that I collected from the community of this forum
Next I show how my code is, would you know how to implement more parameters?
'Exportar respuestas (export_responses)
sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
export64 = jsonObject("result")
Please Log in to join the conversation.