O
OJFEnterprises
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
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