INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Want to thank those people who have made this forum such a valuable place to visit each day..."
Geography
Where in the world do Tek-Tips members come from?
|
Create Chart in Form/Report Using VBA
|
|
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 |
|
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: Charts & VBA Skip,
 Just traded in my old subtlety...
for a NUANCE! |
|
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 CODE'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 |
|
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 |
|
EXCEL CHART! Not Graph! Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
Quote (ExcelHELP)If you have Excel installed, you can also take advantage of the powerful Excel charting functionality in other 2007 Microsoft Office system programs...
Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
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 |
|
Yes it is!!! It is NOT MS Graph! This may help http://www.databasejournal.com/features/msaccess/article.php/3764206/Creating-Charts-in-an-Access-2007-Database.htm Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
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 |
|
Please post your code that demonstrates this issue. Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
Quote: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,
Just traded in my old subtlety...
for a NUANCE! |
|
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. CODEWith cht
.SeriesCollection.Add
End With I'm getting error 438: Object doesn't support this property or method. Max Hugen
Australia |
|
If you check VBA Help, you'll see that the add method has one required argument... Quote: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,
Just traded in my old subtlety...
for a NUANCE! |
|
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 |
|
Take a look at the NewSeries method. Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
Tried that too, same error: Object doesn't support this property or method. Max Hugen
Australia |
|
What is the pedigree of cht? Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
CODEDim cht As Chart
Set cht = Reports(rptName).Controls(chtName).Object Max Hugen
Australia |
|
Does your Watch Window indicate the TYPE of object? Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
Hi Skip, many thanks for your perseverance and patience. I had to crash at 2am last night. CODEDebug.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 |
|
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... CODEdim 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,
Just traded in my old subtlety...
for a NUANCE! |
|
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 (IS/IT--Management) |
15 Jun 12 13:48 |
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. |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One