I’m trying to replicate the manual export results with the token attributes with vba in excel:
Code:
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, FromResponse As String, ToResponse As String
Dim export64 As String, export64Decoded As String
limeurl = "/index.php/admin/remotecontrol"
limeuser = "user"
limepass = "password"
SurveyID = Application.InputBox("Introduzca el ID de la encuesta que quiera importar", "ID Encuesta")
DocumentType = "csv"
LanguageCode = ""
CompletionStatus = "complete"
HeadingType = "code"
ResponseType = "long"
FromResponse = ""
ToResponse = ""
'Clear page
Cells.Clear
'Initalization
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = limeurl
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; charset=utf-8"
'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 + """,""" _
+ LanguageCode + """,""" + CompletionStatus + """,""" + HeadingType + """,""" + ResponseType + """,""" _
+ FromResponse + """,""" + ToResponse + """],""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
With Application
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = False
End With
'Convert CSV
Sheets("Data").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
But i can not find a way to do it with the 'export_responses' function with the remote_handler (
