Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Export answers to excel in semi-realtime

  • vkuzmin
  • vkuzmin's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
5 years 11 months ago - 5 years 11 months ago #166577 by vkuzmin
Hello.
I want to export new answers to excel without web interface via VBA macroses or (if its even possible) Import dialog.
Can you provide some tips or may be even workbook?
I'm trying to get session key but i get HTTP 200 error "Failed to parse request" with XML-RPC or "unable to decode malformed json" with JSON-RPC instead.
Code:
Sub sessionkey_test()
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "http://INSERT-YOUR-SITE.COM/limesurvey/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"
objHTTP.Send ("get_session_key(USERNAME,PASSWORD)")
'objHTTP.Send ("USERNAME,PASSWORD,get_session_key()")
'^ i've tried this way also
Debug.Print (objHTTP.Status)
Debug.Print (objHTTP.responseText)
End Sub

What i should send to get session key and can you provide an VBA example with "export_responses" function?
Last edit: 5 years 11 months ago by vkuzmin.
The topic has been locked.
  • vkuzmin
  • vkuzmin's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
5 years 11 months ago - 5 years 11 months ago #166578 by vkuzmin
Replied by vkuzmin on topic Export answers to excel in semi-realtime
Finaly i got that key. Even harder part will be next: print answers to the workbook's cells...
Code:
Sub sessionkey_JSON ()
Dim limeuser As String, limepass As String, limeurl As String, URL As String, sendtext as string
limeurl = "http://YOURDOMAIN/limesurvey/index.php"
limeuser = "USERNAME"
limepass = "PASSWORD"
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"
sendtext = "{""method"":""get_session_key"",""params"": [""" + limeuser + """,""" + limepass + """],""id"": 1}"
objHTTP.Send (sendtext)
Debug.Print (objHTTP.Status)
Debug.Print (objHTTP.responseText)
End Sub
 
Sub sessionkey_XML ()
Dim limeuser As String, limepass As String, limeurl As String, URL As String, sendtext as string
limeurl = "http://YOURDOMAIN/limesurvey/index.php"
limeuser = "USERNAME"
limepass = "PASSWORD"
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"
sendtext = "<methodCall><methodName>get_session_key</methodName><params><param><value><string>" + limeuser + "</string></value></param><param><value><string>" + limepass + "</string></value></param></params></methodCall>"
objHTTP.Send (sendtext)
Debug.Print (objHTTP.Status)
Debug.Print (objHTTP.responseText)
End Sub
 
Last edit: 5 years 11 months ago by vkuzmin.
The topic has been locked.
  • vkuzmin
  • vkuzmin's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
5 years 11 months ago - 5 years 11 months ago #166584 by vkuzmin
Replied by vkuzmin on topic Export answers to excel in semi-realtime
And i got responces.
Code:
limeurl = "http://YOURDOMAIN/limesurvey/index.php"
limesessionkey = "SESSIONKEY"
limesid = "SID"
limedoctype = "csv" 'xls, csv, json
limelang = "en" 'ru
limecompletionstatus = "complete" 'complete,incomplete, all
sendtext = "<methodCall><methodName>export_responses</methodName><params><param><value><string>" + limesessionkey + "</string></value></param><param><value><int>" + limesid + "</int></value></param><param><value><string>" + limedoctype + "</string></value></param><param><value><string>" + limelang + "</string></value></param><param><value><string>" + limecompletionstatus + "</string></value></param></params></methodCall>"
Last edit: 5 years 11 months ago by vkuzmin.
The topic has been locked.
More
5 years 11 months ago #167212 by innovatec
Replied by innovatec on topic Export answers to excel in semi-realtime
Dear vkuzmin,

The first part of the code you've published I've got to work, however, "and I got responses" does not seem to work.
Could you let me know which references are selected in the VBA?
I got the microsoft scripting runtime, microsoft winHTTP services 5.1, and microsoft Xml v6.

You seem to have it working. I would like to have it work in a way that the answer to the first question of limesurvey is placed in sheets(1).Range("A1"), the answer to question 2 in an other cell and so on.

Any help would be appreciated.

Best regards
The topic has been locked.
  • vkuzmin
  • vkuzmin's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
5 years 11 months ago - 5 years 11 months ago #167217 by vkuzmin
Replied by vkuzmin on topic Export answers to excel in semi-realtime
You need to send that "sendtext" variable, ask responseText, trim it from xml or use xml parser, decode it from base64 ans save to disk or use csv parser.

Something like:
Code:
SendText = "<methodCall><methodName>export_responses</methodName><params><param><value><string>" + LimeSessionKey + "</string></value></param><param><value><int>" + limeSID + "</int></value></param><param><value><string>" + LimeDocType + "</string></value></param><param><value><string>" + LimeLang + "</string></value></param><param><value><string>" + LimeCompletionStatus + "</string></value></param></params></methodCall>"
Resp = SendPOSTRequest(Url, SendText)'this function return objHTTP.responseText as string
Resp = FindAndTrim(Resp, "<string>", "</string>")'FindAndTrim("12345","1","4") return "23"
RespUnencode = Decode64(Resp)'i've found this function here: http://www.vbforums.com/showthread.php?379072-VB-Fast-Base64-Encoding-and-Decoding
Call SaveTextToFile(LimeFileName, RespUnencode)'subprocedure that save base64 unencoded text to file


It's a lot of code but it isn't hard. I'm not sure that I have the right to spread the final version plus i didn't checked that survey is exist and running, sessionkey is valid. Actually i dont parse XML, i just trim its data from tags.
Last edit: 5 years 11 months ago by vkuzmin.
The following user(s) said Thank You: Augustin_P
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose