Suppressing macrosecurity module security dialog

Tue, 22 Mar 2011 10:45:42 GMT

Hi,

I created a macro that writes entries onto a MS Excel spreadsheet on a shared path. It works great except that when a new user tries to access the document from Server 9. The macro when run returns a Parse Macro Failured message.

Some users receive a "Module Security for Document" window. If the user has the luxuary of getting this message they can correctly select 'Allow any macro (only for trusted documents)', and the macro is likely to work fine. However it doesnt always appear.

As a QlikView developer im unsure how to deploys a fix so that all users can use the macro without having the burden of selecting the corrrect macro configuration.

From my initial research I found that the macro configuration should be 'Allow any Macro (only for trusted documents)'. But this option doesn't always appear on the new user PC.

The following user forums are useful but there seems to be conflicting fixes and im a little unsure about registry changes.

http://www.qlikblog.at/523/qliktip-19-suppressing-macrosecurity-module-security-dialog-qlikviewserverqlikviewdocuments/
http://community.qlik.com/forums/t/33774.aspx
http://community.qlik.com/forums/t/16005.aspx
http://community.qlik.com/forums/t/17220.aspx

For your reference here is my macro script.
-------------------------------------------------------------------------------------------------------------------------------------
Sub SubmitQuestionnaire
Set oXL=CreateObject("Excel.Application")
"Username").GetContent.String
set DATA1 = ActiveDocument.GetApplication.GetProperties
now()
set temp = ActiveDocument.GetApplication.GetProperties
mid(temp.UserName,4)
set vCommentsToExcel = ActiveDocument.Variables("vUserEmail")
String
set vCommentsToExcel = ActiveDocument.Variables("vUserPhone")
String
set vCommentsToExcel = ActiveDocument.Variables("Quest1Answer")
String
set vCommentsToExcel = ActiveDocument.Variables("Quest2Answer")
String
set vCommentsToExcel = ActiveDocument.Variables("Quest3Answer")
String
set vCommentsToExcel = ActiveDocument.Variables("vOtherFeedback")
String
"\\Nmh-bus01\QVDataSource\QlikView Questionnaires\Question Answers.xls"
set oWB = oXL.Workbooks.Open(f_name)
set oSH = oWB.Worksheets.Item(1)
"a1:a1000")
Set c = .Find(strIndex, , , 2, 1, 2, 0)
If Not c Is Nothing Then
Do
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?")
set oSH = Nothing
set oWB = nothing
set oXL = nothing

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

'PASTE THE VALUES INTO EXCEL SHEET SPECIFIED
'username index
"A65536").End(-4162).Offset(1,0).FormulaR1C1 = strIndex
'date submitted
"A65536").End(-4162).Offset(0,1).FormulaR1C1 = VarHolder1
'user login
"A65536").End(-4162).Offset(0,2).FormulaR1C1 = VarHolder2
'optional user contact details
"A65536").End(-4162).Offset(0,3).FormulaR1C1 = VarHolder3
"A65536").End(-4162).Offset(0,4).FormulaR1C1 = VarHolder4
'question answers 1-3
"A65536").End(-4162).Offset(0,5).FormulaR1C1 = VarHolder5
"A65536").End(-4162).Offset(0,6).FormulaR1C1 = VarHolder6
"A65536").End(-4162).Offset(0,7).FormulaR1C1 = VarHolder7
'additional feedback
"A65536").End(-4162).Offset(0,8).FormulaR1C1 = VarHolder8
If f_name="False" then
Set oXL=nothing
Exit sub
End If
Set oSH=nothing
Set oWB=nothing
Set oXL=nothing

'CLEAR SELECTIONS AFTER SUBMISSION
set v = ActiveDocument.Variables("vUserEmail")
"",true
set v = ActiveDocument.Variables("vUserPhone")
"",true
set v = ActiveDocument.Variables("Quest1Answer")
"",true
set v = ActiveDocument.Variables("Quest2Answer")
"",true
set v = ActiveDocument.Variables("Quest3Answer")
"",true
set v = ActiveDocument.Variables("vOtherFeedback")
"",true
msgbox("Thank you for completing the 'March 2011 - QlikView Questionnaire. Your answers will be reflected in the next report refresh.")
end if
End Sub
-------------------------------------------------------------------------------------------------------------------------------------

Please help?

Suppressing macrosecurity module security dialog

Tue, 22 Mar 2011 15:00:09 GMT

I forgot to attach the error pop up the user receives if they dont have the correct macro setup.

The error is 'Macro parse failed. Functionality was lost.

Suppressing macrosecurity module security dialog

Tue, 12 Apr 2011 13:20:07 GMT

In macro mudule (ctrl+m) you need to choose System access under Requested macro security and Allow system access under Current local security. And when asked when you open the document you need to choose allow any macro. And of course you need excel installed on that server (and opened at least once 🙂 to confirm all the messages)

Lukasz

topic Suppressing macrosecurity module security dialog in QlikView

Suppressing macrosecurity module security dialog

Suppressing macrosecurity module security dialog

Suppressing macrosecurity module security dialog