6 Replies Latest reply: Jan 20, 2011 11:29 AM by robrobin RSS

    Macro functionality lost between QV 8.5 and 10

      In the process of upgrading from 8.5 to 10. An existing macro (in 8.5) which rebuilds a pivot table by adding/removing dimensions and enabling/disabling expressions, based on two list boxes populated by an inline load, has worked fine. Now, in 10, the macro fails, resulting in one of two errors; 1) Allocated memory exceeded, and 2) the pivot table grayed out with the diagonal lines stretching from corner to corner. Upon checking the properties of the pivot table after the macro has run (activated by a button via Run Macro), the dimensions and expressions both exhibit the correct characteristics as modified by the macro, and then, without altering anything, upon selecting OK in the properties dialogue box, the pivot table proceeds to refresh and display correctly. The macro is included below.

       

      Any ideas as to why this might be? Maybe i need to refresh the chart in a new way?

       

      Thanks for your help.

       

      sub rebuild()
      set vAllowCalc = ActiveDocument.Variables("vAllowCalc")
      vAllowCalc.SetContent 0, true 'Display the "Please wait..." text object
      ActiveDocument.GetApplication.WaitForIdle 'wait for QlikView to finish before modifying report
      set selectedExpressions = ActiveDocument.getField("Expr").getSelectedValues
      set selectedDimensions = ActiveDocument.getField("DimField").getSelectedValues

      CollapseCht

      selectedExpressionMax = selectedExpressions.Count - 1
      selectedDimensionMax = selectedDimensions.Count - 1
      if selectedExpressionMax + selectedDimensionMax > -2 then 'bypass if nothing is selected
      '--------------------------- TABLE BOX ONLY? ---------------------------------------------------
      if selectedExpressionMax = -1 then
      set table = ActiveDocument.getSheetObject("TB01")
      tableDimensionMax = table.GetColumnCount - 1
      for i = tableDimensionMax to 0 step -1 'remove existing dimensions
      table.removeField(i)
      next
      for j = 0 to selectedDimensionMax 'add selected dimensions
      table.addField(selectedDimensions.Item(j).Text)
      next

      else
      '--------------------------- EXPRESSIONS ------------------------------------------------------
      set chart = ActiveDocument.getSheetObject("CH642")
      set chartProperties = chart.GetProperties
      set chartExpressions = chartProperties.Expressions
      chartExpressionMax = chartExpressions.Count - 1
      dim selectedExpression(100) 'runs slightly faster if first set up a simple array
      for j = 0 to selectedExpressionMax
      selectedExpression(j) = selectedExpressions.Item(j).Text
      next
      for i = 0 to chartExpressionMax 'for every possible expression defined to the chart
      chartExpression = chartExpressions.Item(i).Item(0).Data.ExpressionVisual.Label.v
      expression = chartExpressions.Item(i).Item(0).Data.ExpressionData
      expression.Enable = false 'disable the expression
      for j = 0 to selectedExpressionMax 'check all selected expressions
      if selectedExpression(j) = chartExpression then 'if user selected the expression
      expression.Enable = true 'enable it
      j = 9999 'and exit the inner loop
      end if
      next
      next
      ActiveDocument.GetApplication.WaitForIdle
      chart.SetProperties chartProperties
      '--------------------------- DIMENSIONS --------------------------------------------------------
      set vDimCnt = ActiveDocument.Variables("vChtDimCnt")
      vDimCnt2 = vDimCnt.GetContent.String
      chartDimensionMax = vDimCnt2
      for i = chartDimensionMax to 0 step -1 'remove existing dimensions
      chart.removeDimension(i)
      next
      for j = 0 to selectedDimensionMax 'add selected dimensions
      chart.addDimension(selectedDimensions.Item(j).Text)
      next
      end if
      end if

      ActiveDocument.Variables("vChtDimCnt").SetContent selectedDimensionMax,true
      vAllowCalc.SetContent 1, true 'allow dynamic report to recalculate
      ActiveDocument.Variables("vRefrPrompt").SetContent "0",true
      end sub