Referencing an Excel Chart in VBA

[theburnetts]

I am working on a form in MS Access. In design mode I click on Insert
| Object from the main menu. Then I select Microsoft Excel Chart.
This puts a new Excel chart on my Access form (it seems to have some
default data). Now I want to use VBA to modify this chart at run time.
However I have no idea how to reference the chart in code. Let's say
that I name the control myChart. In code when I inspect the properties
and methods of myChart it seems to be just some generic object
properties and methods. Is this object some sort of OLE container or
something? I don't know very much about how it works when you insert
another object on to an Access form (or VB form for that matter). Is
there some magic way that I have to reference the object so that I can
get at the Excel object model? Do I have to create an instance of the
Excel Application and then get at it that way? If so, how do I
ultimately reference myChart to change its properties?
Thanks,
Corey Burnett
(e-mail address removed)

 

[Guest]

The OLE Object in the Excel Chart Object is actually a workbook, not just a
chart (took me a while to figure this out!). The book has two sheets - one a
regular data sheet, the other a chart sheet.

Add the "Microsoft Excel Object Model" to your project's references, then:

Dim myBook as Excel.Workbook
Dim DataSheet as Excel.Worksheet
Dim myChart as Excel.Chart
etc.. (add any other Excel objects, like Excel.Range objects, you may need
to use)

Set MyBook = Forms("MyFormName").ChartControlName.Object
Set DataSheet = MyBook.Sheets("Sheet1")
Set myChart = MyBook.Charts(1)

Now you can refer to the regular Excel objects, properties and methods by
using your object variables.

HTH!
K Dales