Referencing Excel.Chart.8 OLE Object in Access Report

[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

 

[Duane Hookom]

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

 

[OJFEnterprises]

Thanks for your response.

I am using the Excel Chart Control instread of the Microsoft Graph
Control because my chart is pretty complex and is beyond the
limitations of the graph control. I have three charts chart types
(column, line, and area) in the same physical chart space as well as
other customizations that are not supported in the graph control.
So...I am limited to doing the dynamic creation of charts using an
Excel Chart Control.

That said...do you have any other ideas? Maybe I need to back to the
drawing board some because maybe the general approach might be leading
me in the wrong direction. However, in the end, I need to use an
Excel Chart Control (or other chart/graph control that has the
features I need), and then be able to programatically change the data
for each in a report. This would be similar to have a grouping level
with regular data in an Access report. If I would have 15 grouping
levels with detail records in a report, I need to have 15 charts with
the data in each chart reference what would normally be in the detail
records.

Is that clear as mud???

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

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!

 

[Duane Hookom]

You have gone beyond where I have. ACG Soft might have some information at
http://ourworld.compuserve.com/homepages/attac-cg/.

--
Duane Hookom
MS Access MVP

Thanks for your response.

I am using the Excel Chart Control instread of the Microsoft Graph
Control because my chart is pretty complex and is beyond the
limitations of the graph control. I have three charts chart types
(column, line, and area) in the same physical chart space as well as
other customizations that are not supported in the graph control.
So...I am limited to doing the dynamic creation of charts using an
Excel Chart Control.

That said...do you have any other ideas? Maybe I need to back to the
drawing board some because maybe the general approach might be leading
me in the wrong direction. However, in the end, I need to use an
Excel Chart Control (or other chart/graph control that has the
features I need), and then be able to programatically change the data
for each in a report. This would be similar to have a grouping level
with regular data in an Access report. If I would have 15 grouping
levels with detail records in a report, I need to have 15 charts with
the data in each chart reference what would normally be in the detail
records.

Is that clear as mud???

"Duane Hookom" <[email protected]> wrote in message

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

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!

 

[OJFEnterprises]

Thank you much Duane for you help. I'm gonna check out that link in a
little bit.

Since posting that message, I've gotten about 75% of the way there.
Unfortunately it's late binding so I'm flying blindly when trying to
control various aspects of the instance of Excel that opens through
Automation, so my next step is to figure out which Excel object(s) I
can use explicitly to do this early binding...but ultimately, it
works. When I get some of the final code, I'll try to post a skeleton
of it here for others' benefit down the line. But, here's the basic
code to do it:

Dim objExcelChart As Object

Set objExcelChart = Me.oleExcelChart ' name of unbound object frame
objExcelChart.Verb = acOLEVerbShow
objExcelChart.Action = acOLEActivate

' loop would be here to change change cell data
objExcelChart.Object.Application.Worksheets("ChartData").Range("A1").Value
= "Week1"

objExcelChart.Object.Application.Worksheets("ChartData").Range("B1").Value
= "123"

You have gone beyond where I have. ACG Soft might have some information at
http://ourworld.compuserve.com/homepages/attac-cg/.

--
Duane Hookom
MS Access MVP

Thanks for your response.

I am using the Excel Chart Control instread of the Microsoft Graph
Control because my chart is pretty complex and is beyond the
limitations of the graph control. I have three charts chart types
(column, line, and area) in the same physical chart space as well as
other customizations that are not supported in the graph control.
So...I am limited to doing the dynamic creation of charts using an
Excel Chart Control.

That said...do you have any other ideas? Maybe I need to back to the
drawing board some because maybe the general approach might be leading
me in the wrong direction. However, in the end, I need to use an
Excel Chart Control (or other chart/graph control that has the
features I need), and then be able to programatically change the data
for each in a report. This would be similar to have a grouping level
with regular data in an Access report. If I would have 15 grouping
levels with detail records in a report, I need to have 15 charts with
the data in each chart reference what would normally be in the detail
records.

Is that clear as mud???

"Duane Hookom" <[email protected]> wrote in message

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

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!

 

[Duane Hookom]

If you want to set various chart properties, did you note how I suggested to
capture the code in my first post?

--
Duane Hookom
MS Access MVP

Thank you much Duane for you help. I'm gonna check out that link in a
little bit.

Since posting that message, I've gotten about 75% of the way there.
Unfortunately it's late binding so I'm flying blindly when trying to
control various aspects of the instance of Excel that opens through
Automation, so my next step is to figure out which Excel object(s) I
can use explicitly to do this early binding...but ultimately, it
works. When I get some of the final code, I'll try to post a skeleton
of it here for others' benefit down the line. But, here's the basic
code to do it:

Dim objExcelChart As Object

Set objExcelChart = Me.oleExcelChart ' name of unbound object frame
objExcelChart.Verb = acOLEVerbShow
objExcelChart.Action = acOLEActivate

' loop would be here to change change cell data
objExcelChart.Object.Application.Worksheets("ChartData").Range("A1").Value
= "Week1"

objExcelChart.Object.Application.Worksheets("ChartData").Range("B1").Value
= "123"

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message

You have gone beyond where I have. ACG Soft might have some information at
http://ourworld.compuserve.com/homepages/attac-cg/.

--
Duane Hookom
MS Access MVP

Thanks for your response.

I am using the Excel Chart Control instread of the Microsoft Graph
Control because my chart is pretty complex and is beyond the
limitations of the graph control. I have three charts chart types
(column, line, and area) in the same physical chart space as well as
other customizations that are not supported in the graph control.
So...I am limited to doing the dynamic creation of charts using an
Excel Chart Control.

That said...do you have any other ideas? Maybe I need to back to the
drawing board some because maybe the general approach might be leading
me in the wrong direction. However, in the end, I need to use an
Excel Chart Control (or other chart/graph control that has the
features I need), and then be able to programatically change the data
for each in a report. This would be similar to have a grouping level
with regular data in an Access report. If I would have 15 grouping
levels with detail records in a report, I need to have 15 charts with
the data in each chart reference what would normally be in the detail
records.

Is that clear as mud???

"Duane Hookom" <[email protected]> wrote in message

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

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!

 

[OJFEnterprises]

Duane -

Yes, I did pick up on that...I love that little trick (start recording
a macro, do your thing, then see what the VBA looks like.)

My problem now is when I instantiate the Object, I need to figure out
how to hide the instance of Excel that opens. It's a pretty cool
effect (you actually see the chart "painting" across the screen for
each instance of the chart printed in the report). I've trapped for
things like if the instance of Excel is closed while building the
chart, but best case, I would like for it to happen in the background
without the user knowing an instance is actually opened. If they
close MSExcel.exe process in Task Manager, I'm trapping for that too.

I just did the following code:
objExcelChart.Object.Application.Visible = False
DoEvents

....but it's still a bit clumbsy since there are tons of calls between
the objects. I am about 95% of the way there as far as the dynamic
chart building is concerned.

Thanks a lot for your help!

If you want to set various chart properties, did you note how I suggested to
capture the code in my first post?

--
Duane Hookom
MS Access MVP

Thank you much Duane for you help. I'm gonna check out that link in a
little bit.

Since posting that message, I've gotten about 75% of the way there.
Unfortunately it's late binding so I'm flying blindly when trying to
control various aspects of the instance of Excel that opens through
Automation, so my next step is to figure out which Excel object(s) I
can use explicitly to do this early binding...but ultimately, it
works. When I get some of the final code, I'll try to post a skeleton
of it here for others' benefit down the line. But, here's the basic
code to do it:

Dim objExcelChart As Object

Set objExcelChart = Me.oleExcelChart ' name of unbound object frame
objExcelChart.Verb = acOLEVerbShow
objExcelChart.Action = acOLEActivate

' loop would be here to change change cell data
objExcelChart.Object.Application.Worksheets("ChartData").Range("A1").Value
= "Week1"

objExcelChart.Object.Application.Worksheets("ChartData").Range("B1").Value
= "123"

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message

You have gone beyond where I have. ACG Soft might have some information at
http://ourworld.compuserve.com/homepages/attac-cg/.

--
Duane Hookom
MS Access MVP


Thanks for your response.

I am using the Excel Chart Control instread of the Microsoft Graph
Control because my chart is pretty complex and is beyond the
limitations of the graph control. I have three charts chart types
(column, line, and area) in the same physical chart space as well as
other customizations that are not supported in the graph control.
So...I am limited to doing the dynamic creation of charts using an
Excel Chart Control.

That said...do you have any other ideas? Maybe I need to back to the
drawing board some because maybe the general approach might be leading
me in the wrong direction. However, in the end, I need to use an
Excel Chart Control (or other chart/graph control that has the
features I need), and then be able to programatically change the data
for each in a report. This would be similar to have a grouping level
with regular data in an Access report. If I would have 15 grouping
levels with detail records in a report, I need to have 15 charts with
the data in each chart reference what would normally be in the detail
records.

Is that clear as mud???

"Duane Hookom" <[email protected]> wrote in message

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

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!