Create Chart in Form/Report Using VBA

[maxhugen]

Can anyone point me towards a VBA example on how to create a Chart object (MS Graph) in a form or report from scratch please?

I need to do something like add two series, one with a trendline, etc, and have control over all the formatting options etc. One problem that I've struck is that I cannot change or add to the series after using the MS Graph wizard. Hence, I'm studying the Object Model for MS Graph, and would use a VBA function to create &/or setup the Chart.

Any pointers would be appreciated!
MTIA

Max Hugen
Australia

 

[SkipVought]

I can take an Excel Chart, which is use by ALL MS Application AFAIK, sue the New Series method and than assign data to the series...

dim ary
with cht
  .seriescollection.newseries
  with .seriescollection(seriescollection.count)
     ary = range(SomeRangeForNewSeries)
     .values = ary
  end with
end with

I would assume that any array could be assigned to the values property of the series, regardless of its source. Notice that the ary is not SET as a range but assigned as an array

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[maxhugen]

Unfortunately, .seriescollection.newseries fails in Access: error 438: Object doesn't support this property or method.

Just wondering... I'm using Access 2007, and have set a reference to the Microsoft Graph 12.0 Object Library. However, when I checked the refs in Excel 2007, I found that there is no ref for MS Graph. Don't know if this is relevant in any way though.

Max Hugen
Australia

 

[bkclaw113]

This may not be a good option if your series are robust, but have you considered pushing the data from your query into excel and using the an excel range to add the series to your Access chart? I have not tried this myself but it may be a different approach to try.