Edit an embedded Excel OLE-chart with VBA 1

[pharcyder]

Hi!

I've embedded an Excel Chart in an Access-Report because the standard MS Graph Chart would not be sufficient.

Now I know how to automate the Chart in Excel, but I do not know how I would access the properties of this embedded OLE-Chart via Visual Basic out of my Access-Report!
I just can't find the proper syntax and object definitions to get to this object and need a little help here to get started.

Thanks in advance,
Jens

 

[SkipVought]

Jens,

First you ,ust set a reference to the Microsoft Excel Object Library in the VBE Tools/Refereces.

Then use the GetObject method to get the Excel Application object for your embedded chart.

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[pharcyder]

Tried this, but i still can't access the properties of the embedded Excel chart.
If anyone could provide a step by step procedure or some lines of code I would be very happy.

Thanks,
Jens

 

[SkipVought]

Please post you code and reference where you are having the problem.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[pharcyder]

Well, if there would be some code I would surely post it... but as I said I am stuck at the beginning!

I just embedded an Excel.Chart.8 in an Access Report and now I try to automate it via Visual Basic, so that this chart can be filled with data from recordsets or so that I can change the chart type.

I tried this with

    Dim XLChart As Excel.Chart 'Or Application or Object both doesn't work    
    
    Set XLChart = GetObject(Me.OLEEmbeddedChart, Excel.Application) '(".Chart" is not available)

but getting various errors, although I set the references to the Excel Object Library and Data Access Objects...

Well, and what I would need are the first 2 or 3 lines, but I can't even find something on other sites, is there really noone who used an Excel.Chart in an Access report?

 

[Hap007]

If you go to our web site links page

http://www.fcs-software.com/fcslinks.html

There is a link to MS that defines the Excel Object in great detail.

Good Luck,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[pharcyder]

Thanks Hap007, but I even have the MS Excel Visual Basic Book from Microsoft and there are no hints how this could possibly work. They just focus on the charts directly in Excel and other stuff, but not how I address an embedded Excel Chart in another application.

Greetings,
Jens

 

[SkipVought]

I did a test in Powerpoint and was able to change the title. Here's my code

    Dim xlWb As Excel.Workbook, xlChart As Excel.Chart
    Set xlWb = ActivePresentation.Slides(1).Shapes(1).OLEFormat.Object
    Set xlChart = xlWb.Charts(1)
    With xlChart
        .HasTitle = True
        .ChartTitle.Text = "this is a test"
    End With
    
    Set xlChart = Nothing
    Set xlWb = Nothing

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[pharcyder]

Thanks for your effort, SkipVought, but there are no Shapes in Access.
If I try to "Set" the chart-object I get an error message telling me that there is no OLE-Object. Is OLEFormat the name of your object?

Greetings,
Jens

 

[SkipVought]

Use your VB debugger to step into a test procedure and then use the Watch windoew to EXPLORE the current objects, in particular the embedded Excel Chart, to see what the heirarchy (parent/child objects) is.

How to use the Watch Window as a Power Programming Tool faq707-4594



Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[pharcyder]

Did this, I can even find the object over the object explorer, but it still won't work. There is just stuff/porperties/names I already knew. I just can't create a connection to this chart (or worksheet).

And it seems like it would work in Word or PowerPoint, but I can find nothing to make it work for reports in Access.

SkipVought, could you perhaps try out what you done in PowerPoint in an Access report? Then I would be sure I don't get the definitions or syntax wrong but perhaps have a wrong reference or DLL or something!

Is there really noone who ever embedded a chart in a report and changed something via Visual Basic?

Greetings,
Jens

 

[SkipVought]

I'm not an Access guru. How did you get the chart into your report?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[SkipVought]

Just used my Watch Window and figgered it out...

Sub test()
    Dim rpt As Report
    With Access.Reports(0)
        With .Controls(.Controls.Count - 1).Object.Charts(1)
            .HasTitle = True
            .ChartTitle.Text = "New Title"
        End With
    End With
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[pharcyder]

Okay, many thanks SkipVought, got it working in a form now, but in a report the object still makes problems.

Is there a way to set this control to a variable, cause I can't seem to get GetObject to work?
It would just be nice to have the properties available in Visual Basic.

Greeting,
Jens

 

[pharcyder]

Okay, figured it out, you just don't need the GetObject function, you can just set the object without it!

Then your code would be:

Sub test()
 Dim xlc as Excel.Chart
 Set xlc = Me.Controls(0).Object.Charts(1) 'or whatever controlnr. it is..
 With xlc
   .HasTitle = True
   .ChartTitle = "Test"
 End With