Macro functionality lost between QV 8.5 and 10

Wed, 17 Nov 2010 23:37:21 GMT

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

Macro functionality lost between QV 8.5 and 10

Thu, 18 Nov 2010 16:26:03 GMT

One last shot here.... Any ideas?

Thanks

Macro functionality lost between QV 8.5 and 10

Thu, 18 Nov 2010 16:28:50 GMT

One last shot here... Any ideas?

Thanks

Macro functionality lost between QV 8.5 and 10

jp_golay — Tue, 18 Jan 2011 17:14:10 GMT

I have same problem the addfield get the tablebox graxed with a cross.

Have you found a solution

Thanks

Macro functionality lost between QV 8.5 and 10

Wed, 19 Jan 2011 00:17:25 GMT

I called the Expand/Collapse function we already had coded, I just called them again as a part of the other macro at the end...

Sub ExpandCht
set chart = ActiveDocument.getSheetObject("CH642")
set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = true
chart.SetProperties gp

End Sub

Sub CollapseCht
set chart = ActiveDocument.getSheetObject("CH642")
set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = false
chart.SetProperties gp

End Sub

Does that make sense...?

Macro functionality lost between QV 8.5 and 10

Thu, 20 Jan 2011 16:17:18 GMT

Hi Rob,

Do you have resolution for this yet ? I have the same problem.

And adding the expand sub macro in the original macro still does not work.

Macro functionality lost between QV 8.5 and 10

Thu, 20 Jan 2011 16:29:28 GMT

Beyond the Expand/Collapse macro calls, I have no other solutions. The macros fixed my problems so I stopped troubleshooting. Sorry I can't be of more help.

topic Macro functionality lost between QV 8.5 and 10 in QlikView

Macro functionality lost between QV 8.5 and 10

Macro functionality lost between QV 8.5 and 10

Macro functionality lost between QV 8.5 and 10

Macro functionality lost between QV 8.5 and 10

Macro functionality lost between QV 8.5 and 10

Macro functionality lost between QV 8.5 and 10

Macro functionality lost between QV 8.5 and 10