topic QlikView Questionnaire - Help needed with macro VB in QlikView

QlikView Questionnaire - Help needed with macro VB

Fri, 18 Mar 2011 16:41:41 GMT

Hi,

I created a useful qlikview questionnaire based on a report i found on the forum.

The thing is i need help with the macro script (script below, also QVW attached).

Currently the user has to select the name from a list box. I want this amended so that the macro script uses the variable 'username' which is a system generated id (OSUser()). Therefore eliminating the need for the user to select their name. Does anybody know how i should amend the code below? I think the italics part needs to be configured.

--------------------------------------------

Sub SubmitQuestionnaire

Set oXL=CreateObject("Excel.Application")

set doc = ActiveDocument
set mySelection = doc.fields("User").GetSelectedValues
for i = 0 to mySelection.Count -1
strIndex = mySelection.Item(i).text
next

set DATA1 = ActiveDocument.GetApplication.GetProperties
VarHolder1 = now()
set temp = ActiveDocument.GetApplication.GetProperties
VarHolder2 = mid(temp.UserName,4)
set vCommentsToExcel = ActiveDocument.Variables("Quest1Answer")
VarHolder3 = vCommentsToExcel.GetContent.String
set vCommentsToExcel = ActiveDocument.Variables("Quest2Answer")
VarHolder4 = vCommentsToExcel.GetContent.String
set vCommentsToExcel = ActiveDocument.Variables("Quest3Answer")
VarHolder5 = vCommentsToExcel.GetContent.String
set vCommentsToExcel = ActiveDocument.Variables("vOtherFeedback")
VarHolder6 = vCommentsToExcel.GetContent.String

f_name = "\\Nmh-bus01\QVDataSource\QlikView Questionnaires\Question Answers.xls"
'f_name = "Question Answers.xls"
set oWB = oXL.Workbooks.Open(f_name)
set oSH = oWB.Worksheets.Item(1)

With oWB.Worksheets.Item(1).Range("a1:a1000")
Set c = .Find(strIndex, , , 2, 1, 2, 0)
If Not c Is Nothing Then
firstAddress = c.Address
Do
VarHolder = VarHolder + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

if VarHolder = 100 then
msgbox("It appears you have already submitted a questionnaire. To resubmit please delete old questionnaire?")
oWB.Close

set oSH = Nothing
set oWB = nothing
set oXL = nothing

'CLEAR SELECTIONS IF QUESTIONNAIRE ALREADY SUBMITTED
set v = ActiveDocument.Variables("Quest1Answer")
v.SetContent "",true
set v = ActiveDocument.Variables("Quest2Answer")
v.SetContent "",true
set v = ActiveDocument.Variables("Quest3Answer")
v.SetContent "",true
set v = ActiveDocument.Variables("vOtherFeedback")
v.SetContent "",true
exit sub
else

'PASTE THE VALUES INTO EXCEL SHEET SPECIFIED
'username index
oSH.Range("A65536").End(-4162).Offset(1,0).FormulaR1C1 = strIndex
'date submitted
oSH.Range("A65536").End(-4162).Offset(0,1).FormulaR1C1 = VarHolder1
'user login
oSH.Range("A65536").End(-4162).Offset(0,2).FormulaR1C1 = VarHolder2
'question answers 1-3
oSH.Range("A65536").End(-4162).Offset(0,3).FormulaR1C1 = VarHolder3
oSH.Range("A65536").End(-4162).Offset(0,4).FormulaR1C1 = VarHolder4
oSH.Range("A65536").End(-4162).Offset(0,5).FormulaR1C1 = VarHolder5
'additional feedback
oSH.Range("A65536").End(-4162).Offset(0,6).FormulaR1C1 = VarHolder6

If f_name="False" then

Set oXL=nothing
Exit sub
End If

oWB.Save
oWB.Close

Set oSH=nothing
Set oWB=nothing
Set oXL=nothing

'CLEAR SELECTIONS AFTER SUBMISSION
set v = ActiveDocument.Variables("Quest1Answer")
v.SetContent "",true
set v = ActiveDocument.Variables("Quest2Answer")
v.SetContent "",true
set v = ActiveDocument.Variables("Quest3Answer")
v.SetContent "",true
set v = ActiveDocument.Variables("vOtherFeedback")
v.SetContent "",true

msgbox("Thank you for completing the 'March 2011 - QlikView Questionnaire. Your answers will be reflected in the next report refresh.")
ActiveDocument.Reload
ActiveDocument.ClearAll
end if
End Sub

-------------------------------------------------------------------

Any help would be great.

Thanks Jason

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0574.QVW.zip:550:0]

QlikView Questionnaire - Help needed with macro VB

Oleg_Troyansky — Fri, 18 Mar 2011 18:26:36 GMT

You have a couple of alternatives, better than Macro:

1. You can link your User field with the Section Access USERID (or NTName) and request "Data reduction based on Section Access", and this way when the user logs in, only one user name will be available

2. Another option would be to use an Action and force a selection in the field, with the value derived from function OSUser(). Depending on your needs, you can trigger an action at the required time.

cheers,

QlikView Questionnaire - Help needed with macro VB

Fri, 18 Mar 2011 19:26:25 GMT

Hi Oleg, many thanks for your solutions.

Would you be kind enough to show examples of how i could amend the script?

Regards

Jason

QlikView Questionnaire - Help needed with macro VB

Oleg_Troyansky — Fri, 18 Mar 2011 19:40:17 GMT

Jason,

this is kinds of a long story... it's well documented, though - look at the Reference Guide, chapter "Security"

QlikView Questionnaire - Help needed with macro VB

Fri, 18 Mar 2011 19:58:58 GMT

im not sure how your examples work but thanks anyway

what i need is a way to amend the script in the macro so that it uses variable 'username' rather then "user"

-------------

set doc = ActiveDocument
set mySelection = doc.fields("User").GetSelectedValues
for i = 0 to mySelection.Count -1
strIndex = mySelection.Item(i).text
next

---------------

any ideas?

QlikView Questionnaire - Help needed with macro VB

Tue, 22 Mar 2011 16:01:26 GMT

Just thought id let you know that I managed to get this working by replacing the script

set doc = ActiveDocument
set mySelection = doc.fields("User").GetSelectedValues
for i = 0 to mySelection.Count -1
strIndex = mySelection.Item(i).text
next

with the following

strIndex = ActiveDocument.Variables("Username").GetContent.String

Re: QlikView Questionnaire - Help needed with macro VB

vikasmahajan — Fri, 04 Dec 2015 09:19:47 GMT

can you attach sample application ?

vikas