Why can't you use data from an Access table or query and graph using the
control in Access? These are virtually the same from my experience.

I wanted to change the tick labels in an Access graph control but couldn't
find the code.
I:
-opened Excel
-created a sample graph,
-turned on the macro recorder,
-changed the properties I needed to code in Access,
-stopped the recorder
-reviewed the Excel generated code
-copied and pasted the code into Access
-made some small modifications
-ran the report,
-saved the report

Duane

--
Duane Hookom
MS Access MVP
--

"OJFEnterprises" wrote in message
om...
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!