Excel Chart Object In Access Report.

[dorlesky]

I've tried numerous times to get Excel to recognise the Excel Chart Object inserted into an Access Report. No Luck.

I can get Access to start an instance of Excel, I can get Access to send the data to Excel. How do I get Excel to now Recognise the Chart object, so I can use Automation to change the features of the chart?

[MikeTheBike]

I've tried numerous times to get Excel to recognise the Excel Chart Object inserted into an Access Report. No Luck.

I can get Access to start an instance of Excel, I can get Access to send the data to Excel. How do I get Excel to now Recognise the Chart object, so I can use Automation to change the features of the chart?

Hi

You could try recording a macro in Excel changing the features you require, and then transfer the code to Access, with the necessary mods, ie. all ojects to be references via the Excel instance in access etc. ?


MTB

[FishVal]

I've tried numerous times to get Excel to recognise the Excel Chart Object inserted into an Access Report. No Luck.

I can get Access to start an instance of Excel, I can get Access to send the data to Excel. How do I get Excel to now Recognise the Chart object, so I can use Automation to change the features of the chart?

Hi.

I suppose you'll not succed this way bkz Access (my somewhat speculative presumption) does not load object to report object frame control.
Expression in report module like Me.OLE1.Object fails with error message smthng like "Object does not exist".
Moreover, if you place OLE object to a form and then place the form to a report within SubForm control, then any attempt to access subform form object will fail.

My very speculative guess is that access supposes that reports should not contain dynamic objects. It calls correspondent object once to generate picture which is rendered to report page.

[MikeTheBike]

Hi

Without knowing precisley what you are trying to do but I assume you are coding all this in Access, as you said, using automation. If so then this code returns info from Chart object and resizes it.

Private Sub cmdTestChart_Click()
    Dim oExcel As Excel.Application
 
    Set oExcel = CreateObject("Excel.application")
 
    oExcel.Workbooks.Open ("H:\DBForum\Example Database Code etc\TestChart.xls")
 
    Dim chrt As Excel.ChartObject
 
    With oExcel.ActiveSheet
 
        For Each chrt In .ChartObjects
            MsgBox chrt.Name
            .Shapes(chrt.Name).ScaleWidth 1.5, msoFalse, msoScaleFromTopLeft
            .Shapes(chrt.Name).ScaleHeight 1.59, msoFalse, msoScaleFromTopLeft
        Next
    End With
    oExcel.Visible = True
End Sub

HTH

MTB

ps. you also need a reference to the Microsoft Office Object Library