- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to add a calculated dimension to a chart from a VBScript macro ?
Hi all,
I need to create a bunch of graphs and wanted to quickly write a little macro that would replicate the same layout for all the required graphs.
So I started to write some lines of code but quickly hit a problem. Here is the (simplified) code:
Function CreateGraphs()
Set chart = ActiveDocument.ActiveSheet.CreateLineChart
chart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])"
chart.AddExpression "MeasurementValue"
End Function
For some reason, this line "chart.AddDimension" is silently ignored. If I add the same calculated dimension interactively: no problem, all is working perfectly. But I'm unable to reach the same result via the macro.
It looks like this issue has already been raised in a previous post but I could find the right way to workaround the issue.
Any suggestion to help me?
Thanks a lot, have a great day.
--
Gaëtan
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi again,
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Gaetan,
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!
Stefan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Stefan,
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Gaetan,
which version of qv did you use? I'm working on v9 and the code works
Regards!
Stefan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi again,
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks. I'll have it in mind 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Stefan,
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Gaetan,
and i can see the how countinuos in the API
Regards!
Stefan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Stefan,
Stefan Stoichev wrote:
and i can see the how countinuos in the API
What do you mean? Do you know how to force a "continous X-axis" in a VBScript macro?
Have a great evening.
Gaëtan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry Gaetan it was typo and i mean: can't 😞
- « Previous Replies
-
- 1
- 2
- Next Replies »