Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Error when running macro via accesspoint

Hi all,
What am I doing wrong? Is there a way to actually run this macro?
I've attached a screen shot of the error I get.
I have done this on the QMC -
"1. Make sure you are granting System Access to your macros in the server through QEMC (System, Setup, QlikView Servers, expand and click, Seucrity tab in the right pane "Allow unsafe macro execution on server" and "Allow macro execution on server" ticked) and in the Macro editor, (Ctrl + M, bottom left drop downs)
2. If you are creating some filesystem object (such an excel file) make sure you are using the IE Plugin."
This is my macro:
Sub ExcelAddComment
VarHolder1 = 0
set val=ActiveDocument.Fields("RecID").GetPossibleValues
for i=0 to val.Count-1
VarHolder1 = VarHolder1 + 1
next

if VarHolder1 > 1 then
msgbox ("More than one record is showing.  Refine your selection to select only one index.")
exit sub
else
end if

Set oXL=CreateObject("Excel.Application")

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

set vCommentsToExcel = ActiveDocument.Variables("vName")
VarHolder1 = vCommentsToExcel.GetContent.String

set vCommentsToExcel = ActiveDocument.Variables("vGrade")
VarHolder2 = vCommentsToExcel.GetContent.String

set vCommentsToExcel = ActiveDocument.Variables("vStartDate")
VarHolder3 = vCommentsToExcel.GetContent.String

set vCommentsToExcel = ActiveDocument.Variables("vEndDate")
VarHolder4 = vCommentsToExcel.GetContent.String

set temp = ActiveDocument.GetApplication.GetProperties
VarHolder5 = mid(temp.UserName,4)

f_name ="\\\Live QV\Excel Loads\Comments.xls"

set oWB = oXL.Workbooks.Open(f_name)
set oSH = oWB.Worksheets.Item(1)

With oWB.Worksheets.Item(1).Range("a1:a50")
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 = 1 then
msgbox("There already is a record for this item. If you want to change the note, delete it first.")
oWB.Close
set oSH = Nothing
set oWB = nothing
set oXL = nothing
set v = ActiveDocument.Variables("vName")
v.SetContent  "",true
exit sub
else

oSH.Range("A65536").End(-4162).Offset(1,0).FormulaR1C1 = strIndex
oSH.Range("A65536").End(-4162).Offset(0,1).FormulaR1C1 = VarHolder1 'name
oSH.Range("A65536").End(-4162).Offset(0,2).FormulaR1C1 = VarHolder2 'grade
oSH.Range("A65536").End(-4162).Offset(0,3).FormulaR1C1 = VarHolder3 'start
oSH.Range("A65536").End(-4162).Offset(0,4).FormulaR1C1 = VarHolder4 'enddate
oSH.Range("A65536").End(-4162).Offset(0,5).FormulaR1C1 = VarHolder5 'ccode

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
set v = ActiveDocument.Variables("vName")
v.SetContent  "",true
msgbox("Record added")
ActiveDocument.Reload
ActiveDocument.ClearAll
end if
End Sub
Any help would be much appreciated!
2 Replies
Not applicable

Re: Error when running macro via accesspoint

Do you have Excel installed on the QlikView Server?

mwoolf
Honored Contributor II

Re: Error when running macro via accesspoint

Excel doesn't need to be installed on the server.

Do Shift+Ctrl+M and see how the macro security is set. It must be set to system access.

Community Browser