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]

hi,

What application?

What version?

Run and hide from MS Graph. It is clunky! MS Office 2007+ uses the Excel chart object model in all application. Excel CHART is the way to go!

faq707-4811

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

 

[maxhugen]

G'day Skip

I'm using Access 2007, and want to use charts in Access forms &/or reports. I thought that I read somewhere that the Chart object in Access uses the same engine as Excel: MS Graph? (GRAPH.EXE) Although it seems to also be referred to as MS Graph Chart...

In my experimental code, I found that I couldn't dim a variable as type 'chart' in Access even though I'd set a reference to the MS Graph Object Library; seems it has to be type 'object, eg

'Dim cht As Chart - this doesn't work!
Dim cht As Object
Set cht = Reports(rptName).Controls(chtName).Object
Debug.Print cht.application.name   ' returns "Microsoft Graph"

The biggest problem seems to be how to add series etc. I can't find the right way to do this in Access, so I was hoping to find an example of creating a complete chart using VBA to help me.

Max Hugen
Australia

 

[maxhugen]

Hmmm... some minor progress. Looks like I can dim a variable as type Chart in Access. I think it failed when I didn't append the ".object" property to the reference to the chart object at first, and then I didn't back-check after fixing it. My bad.

As to how to add a series (as I'd do in Excel), it appears that in Access it has to be done via the DataSheet object... somehow. I've been googling my eyes red trying to make sense of this object. When I open a basic chart, created by the wizard, I see a DataSheet with the standard sample data in it (East/West/North x Q1/Q2/Q3/Q4). How/when/where does Access 'repopulate' the DataSheet with the information provided by the RowSource of the Chart control?

Something I've yet to figure out.


Max Hugen
Australia

 

[SkipVought]

EXCEL CHART! Not Graph!

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

 

[SkipVought]

If you have Excel installed, you can also take advantage of the powerful Excel charting functionality in other 2007 Microsoft Office system programs...

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

 

[maxhugen]

Now I'm getting even more confused. Isn't the Chart object in A2007 the same as the Excel Chart? If not, how do I add an Excel Chart pls?

Max Hugen
Australia

 

[SkipVought]

Yes it is!!!

It is NOT MS Graph!

This may help

http://www.databasejournal.com/feat...reating-Charts-in-an-Access-2007-Database.htm

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

 

[maxhugen]

Thanks, I've seen that article - plus countless others - as I've floundered about on the web.

Yet the properties of the Chart object show that the OLE Class is Microsoft Graph Chart, and the class is MSGraph.Chart.8, so I don't understand how this is not MS Graph?

The major reason I'm delving into the Object Model is because there seems to be no way to add or edit the 'series' once the Chart object has been created.



Max Hugen
Australia

 

[SkipVought]

Please post your code that demonstrates this issue.

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

 

[SkipVought]

What we've got here is (a) failure to communicate

In Office prior to 2007, there was a klunky chart object that I recall was what you got in PowerPoint and Word, I think.

With 2007, the chart object used by office is identical in each office application, and is mych closer to the kind of object that Excel had. It may be called MS Graph or MS Chart, but it bares no resemblence to that old graph object.



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

 

[maxhugen]

Yeh, I started in Access in '98... and some areas I just didn't bother with... eg charts. They had a terrible rep.

I am trying to add a new Series.

With cht
    .SeriesCollection.Add
End With

I'm getting error 438: Object doesn't support this property or method.

Max Hugen
Australia

 

[SkipVought]

If you check VBA Help, you'll see that the add method has one required argument...

Syntax

expression.Add(Source, Rowcol, SeriesLabels, CategoryLabels, Replace)

expression A variable that represents a SeriesCollection object.

Parameters

Name Required/Optional Data Type Description
Source Required Variant The new data as a Range object.
Rowcol Optional XlRowCol . Specifies whether the new values are in the rows or columns of the specified range.
SeriesLabels Optional Variant True if the first row or column contains the name of the data series. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the series name from the contents of the first row or column.
CategoryLabels Optional Variant True if the first row or column contains the name of the category labels. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the category label from the contents of the first row or column.
Replace Optional Variant If CategoryLabels is True and Replace is True, the specified categories replace the categories that currently exist for the series. If Replace is False, the existing categories will not be replaced. The default value is False.

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

 

[maxhugen]

I've seen this in Excel VBA Help - but it's specific to Excel, as the required argument 'Source' must be a range object, which represents 'a cell, a row, a column, a selection of cells' [in a worksheet]. I don't see how that works in Access.

Max Hugen
Australia

 

[SkipVought]

Take a look at the NewSeries method.

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

 

[maxhugen]

Tried that too, same error: Object doesn't support this property or method.

Max Hugen
Australia

 

[SkipVought]

What is the pedigree of cht?

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

 

[maxhugen]

Dim cht As Chart 
Set cht = Reports(rptName).Controls(chtName).Object

Max Hugen
Australia

 

[SkipVought]

Does your Watch Window indicate the TYPE of object?

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

 

[maxhugen]

Hi Skip, many thanks for your perseverance and patience. I had to crash at 2am last night.

    Debug.Print TypeName(cht)           ' --> Chart
    Debug.Print cht.Application.Name    ' --> Microsoft Graph

I'm also wondering if maybe I need to manually add data from the query to the DataSheet - at least initially.

Max Hugen
Australia