as far i can see AddDimension with calculated dimension is not working on line/bar charts but works if pivot table is created. So my idea is macro first create pivot table with calculated dimension and then change the new created chart type to line chart. The folloing macro do the job:
sub CreateGraphs set chart = ActiveDocument.ActiveSheet.CreatePivotTable set s=ActiveDocument.Sheets("Main") ' loop through all sheet objects to find the chart ID of the new created chart for i=0 to s.NoOfSheetObjects-1 objid = s.SheetObjects(i).GetObjectId ' filter only chart objects because we create chart if left(replace(objid,"Document\",""),2) = "CH" then newchart = s.SheetObjects(i).GetObjectId newchart = replace(newchart,"Document\","") end if next set linechart = ActiveDocument.GetSheetObject(newchart) 'add dimensions and expressions. At this point the chart is still pivot table linechart.AddDimension "=IF(Name='A','AA','Name')" linechart.AddExpression "count(id)" 'change the chart type to line chart linechart.SetChartType 4 ' line End sub
Hope this helps!
Thanks for your help, it indeed looks like a good idea but according to the first tests, I can add the calculated dimension to the pivot tabe (that's a first good news!) but the SetChartType operation does not work...
Here is the code:
Set chart = ActiveDocument.ActiveSheet.CreatePivotTable chart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])" chart.AddExpression "MeasurementValue" If chart.SetChartType(4) Then MsgBox "ok" End If
The message "ok" never appears and I see a pivot table with the correct dimension/expression. If I then change the type "manually", it just works fine.
What can be wrong?
Stefan Stoichev wrote:
which version of qv did you use? I'm working on v9 and the code works
Indeed, I should have mentionned it : I'm using V10 SR2.
Again, thanks for your precious help. Now I'm looking for a solution to change the X-axis to a continous one, It does not look to be exposed in the API, ...? I may need to create a new thread about this one...
Have a great day.
Got it to work...The trick is to add the calculated dimension to a pivot chart, then allow "fast change" to line chart before calling SetChartType. See code below.
Thanks for the top Stefan, it really helped.
' Create new chart ' Note: adding calculated dimension does not work on line charts so let's start ' with a pivot table and change the type later Set pchart = ActiveDocument.ActiveSheet.CreatePivotTable ' Get the object ID pchartid = Replace(pchart.GetObjectId, "Document\", "") ' Add calculated dimension pchart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])" ' Add expression pchart.AddExpression "MeasurementValue" ' Now switch to a "line chart" type; to do so, we first ' need to allow "FastChange" set p = pchart.GetProperties p.GraphLayout.FastChange.Line = true pchart.SetProperties p ' Do change type to Line chart pchart.SetChartType 4 ' Now retrieve the line chart object Set chart = ActiveDocument.GetSheetObject(pchartid) Set p = chart.GetProperties ' Revert the "fastchange" trick to be able to change the type of the graph p.GraphLayout.FastChange.Line = false ' ** ' ** Do your own customization of the line chart object here ** ' ** ' Apply your change chart.SetProperties p