Hello All!

I have created an OLE Object in an Access report containing two Excel
Worksheets (Class = Excel.Chart.8).

The first Worksheet named [Chart] contains...well, a chart. The
second Worksheet is named [ChartData]. The chart references the data
in the [ChartData] worksheet. So far, so good. My OLE Object in the
Access report is named [oleExcelChart].

Okay...what I'm wanting to do is through VBA code, modify the
[ChartData] Worksheet in the Detail section's Print event in the
Access report, so I can dynamically change the Chart for each detail
record print.

I have some ideas on how to code this, but I can't figure out which
object (or combination of objects) to use that will accept the OLE
Object as a parameter and modify it. Here is a skeleton of what I'm
wanting to accomplish--I might be really close or totally off, but
here goes:

------------------------------------------------------------------
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim xls As Excel.Application

Set xls = Me.oleExcelChart ' pass OLE Object to some Excel COM
Object

With xls
.Sheets("ChartData").Select ' select [ChartData] worksheet

' change data for three columns in first data row
.Range("A2").Select
.ActiveCell.FormulaR1C1 = "Week1"
.Range("B2").Select
.ActiveCell.FormulaR1C1 = "123"
.Range("C2").Select
.ActiveCell.FormulaR1C1 = "456"

' change data for three columns in second data row
.Range("A3").Select
.ActiveCell.FormulaR1C1 = "Week2"
.Range("B3").Select
.ActiveCell.FormulaR1C1 = "321"
.Range("C3").Select
.ActiveCell.FormulaR1C1 = "654"

' ...etc
' ...etc
End With

' maybe need some type of save method here???
xls.SomeSaveMethod

Set xls = Nothing
End Sub
------------------------------------------------------------------

Any suggestions?

Thanks!