- Posts: 4
- Thank you received: 3
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
VBA for RemoteControl2 JSON RPC
- Augustin_P
-
Topic Author
- Offline
- New Member
-
Less
More
2 years 11 months ago #197309
by Augustin_P
VBA for RemoteControl2 JSON RPC was created 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):
I hope it will help people.
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):
Code:
' ----------------------------------------------------------------------- ' 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
The topic has been locked.
- DenisChenu
-
- Offline
- LimeSurvey Community Team
-
Less
More
- Posts: 12886
- Thank you received: 2371
2 years 11 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.
Replied by DenisChenu on topic VBA for RemoteControl2 JSON RPC
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 topic has been locked.
- Augustin_P
-
Topic Author
- Offline
- New Member
-
Less
More
- Posts: 4
- Thank you received: 3
2 years 10 months ago #198097
by Augustin_P
Replied by Augustin_P on topic VBA for RemoteControl2 JSON RPC
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.
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
The topic has been locked.
2 years 3 months ago #209722
by SeppeN
Replied by SeppeN on topic VBA for RemoteControl2 JSON RPC
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)
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
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)
Code:
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
The topic has been locked.
- DenisChenu
-
- Offline
- LimeSurvey Community Team
-
Less
More
- Posts: 12886
- Thank you received: 2371
2 years 3 months ago #209723
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.
Replied by DenisChenu on topic VBA for RemoteControl2 JSON RPC
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)
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
The topic has been locked.
2 years 3 months ago #209733
by SeppeN
Replied by SeppeN on topic VBA for RemoteControl2 JSON RPC
It's just without index.php:
limeurl = 'http://12.345.678.901"
And indeed after activating RemoteControl it works.
Thank you guys!
limeurl = 'http://12.345.678.901"
And indeed after activating RemoteControl it works.
Thank you guys!
The topic has been locked.