2 Replies Latest reply: Feb 3, 2010 2:11 AM by Sören Hakola RSS

    Cleanup macro for variables

      Hello.

      I've browsing to find a solution to the following problem, but I could not fnid anything that suits the needs. Can anybody help me?

      Problem:

      QlikView file is reading an Excel- file, containing calculation formulas and putting them into variables (at the time 64 formulas and might grow or shrink). Due to possible changes in the formulas, some variables might be unnecessary during a reload.

      I was trying to find a macro or something that showed me what the possible function calls for the variable collection are. Since ActiveDocument.Variables("varName").Delete does not work (i.e. there is no such sub function!) I cannot reset nor delete the variables.

      I got so far that I had the variable name in a VBScript variable and could get a msgbox to react to the content, but what then?

       

      sub cleanVariables
      Set VariableCollection = ActiveDocument.GetVariableDescriptions
      for i = 0 to VariableCollection.Count - 1
      set tempVarName = VariableCollection.Item(i).Name
      if left(tempVarName,3) = "tot" then
      '' Here I should put some code to
      '' delete the variable, but WHAT??
      end if
      next
      end sub

      Or is there a workaround for this?

      Greetings,

      Sören

        • Cleanup macro for variables

          Hi,

          Try this:

          ActiveDocument.RemoveVariable "nameOfVariable"

          Steve

            • Cleanup macro for variables

              Hi Steve,

              This was just what I was looking for. Thank you!

              And for those interested, here's the working code:

               


              sub cleanVariables
              Set VariableCollection = ActiveDocument.GetVariableDescriptions
              for i = 0 to VariableCollection.Count - 1
              set tempVar = VariableCollection.Item(i)
              tempVarName = tempVar.Name
              if left(tempVarName,3) = "tot" then
              'msgbox "Deleting " & tempVarName
              ActiveDocument.RemoveVariable tempVarName
              end if
              next
              end sub


              Greeting,

              Sören