2 Replies Latest reply: Aug 31, 2012 4:14 PM by m w RSS

    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!