Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
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
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,
Hi Oleg, many thanks for your solutions.
Would you be kind enough to show examples of how i could amend the script?
Regards
Jason
Jason,
this is kinds of a long story... it's well documented, though - look at the Reference Guide, chapter "Security"
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?
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
can you attach sample application ?
vikas