VBA for RemoteControl2 JSON RPC

More
1 year 7 months ago #197309 by Augustin_P
Hi,


I made a vba script to ‘automaticaly’ export the answers of a survey to an Excel sheet. I made it thanks to the help of vkuzmin on this forum ( this thread ).

I use JSON RPC.

This is the code I made (I am no developer, I just mixed sources I found online):

' -----------------------------------------------------------------------
' JSON RPC CONNECTIVITY SETTINGS
' -----------------------------------------------------------------------
' This file will contain the settings needed to access json RPC with vba.
'
' This VBA code use two different modules:
' 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
' 
' I used the script made by vkuzmin as a base:
' 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 = "https://www.website.com/index.php"
limeuser = "user"
limepass = "pasword"
SurveyID = "id"
DocumentType = "csv"
 
'Clear page
Cells.Clear
 
'Initalization
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"
 
'Get 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")
 
'Export answers
sendtext = "{""method"":""export_responses"",""params"": [""" + key + """,""" + SurveyID + """,""" + DocumentType + """],""id"": 1}"
objHTTP.Send (sendtext)
jsonText = objHTTP.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
export64 = jsonObject("result")
 
'Decode answers
export64Decoded = Decode64(export64)
 
'Close session
sendtext = "{""method"":""release_session_key"",""params"": [""" + key + """],""id"": 1}"
objHTTP.Send (sendtext)
 
'Divide the respond in multiple lines, otherwise evrything is in one cell
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
 
'Convert 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



I hope it will help people.
The following user(s) said Thank You: DenisChenu, SeppeN

Please Log in to join the conversation.

More
1 year 7 months ago #197419 by DenisChenu

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development . I don't answer to private message.

Please Log in to join the conversation.

More
1 year 7 months ago #198097 by Augustin_P
Hi,

Thanks for your feedback, I’m glad you liked it.

I just added the VBA code to the wiki, that was my objective but I wanted first to have an opinion on it.
The following user(s) said Thank You: DenisChenu

Please Log in to join the conversation.

More
11 months 1 week ago #209722 by SeppeN
If this works for me, it's exactly what I need. So, thank you in avance. I have no experience in Databases with VBA.

After adding the module JsonConverter to modules in the VBA project and adding the module Enc64, I copied the code in Book1-Sheet1.
I changed the variables
(My LimeSurvey runs on an AWS instance, it can be reached by a Public IPv4 address)
limeurl = "http://12.345.678.901/index.php"
limeuser = "user"
limepass = "XXXXXXXXXX"
SurveyID = "123456"
DocumentType = "csv"

After I press run, I get the error:
Compile error:
User-defined type not defined

Fixed it by selecting the "Microsoft Scripting Runtime"-references.

But then, I get the error
Run time error '10001':
Application-defined or object-defined error

Please Log in to join the conversation.

More
11 months 1 week ago #209723 by DenisChenu
limeurl = "http://12.345.678.901/index.php" without index.php ?

See the URL = limeurl + "/admin/remotecontrol" part and update according to your LimeSurvey installation (you must activate remote_control before using it)

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development . I don't answer to private message.
The following user(s) said Thank You: SeppeN

Please Log in to join the conversation.

More
11 months 1 week ago #209733 by SeppeN
It's just without index.php:
limeurl = 'http://12.345.678.901"

And indeed after activating RemoteControl it works.

Thank you guys!

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now