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!