Importing an Excel chart to an Access report

[Marie via AccessMonster.com]

Using Access 2000 I am trying to export Access query results to Excel where
I would like to create a chart. I would then like to import the chart into an
Access report.
The query and export section of the following code is working. The query
result appears in Excel, however the macro I have created in Excel does not
create the chart. When I run the macro in Excel it does create the chart and
copies it to the clipboard, but there seems to be a disconnect somewhere in
the code. I am receiving an "Object Required" message. I am not sure which
object it is referring to.

Sub PreviewChart_Click()
On Error GoTo Err_PreviewChart_Click

DoCmd.OpenQuery "qryMakeTableXXX", acNormal, acEdit
DoCmd.TransferSpreadsheet acExport, 8, "TableXXX", "c:\Cases\XXXChart",
True, ""
objExcel.Application.Run "PERSONAL.XLS!XXXChart"
DoCmd.OpenReport "rptXXXChart", acDesign, "", ""
DoCmd.RunCommand acCmdPaste
Exit_PreviewChart_Click:
Exit Sub

Err_PreviewChart_Click:
MsgBox Err.Description
Resume Exit_PreviewChart_Click

End Sub

I am new to coding and not sure if the code I have created is the best way to
accomplish the task.
I would prefer to create the charts in Excel as I find them much easier and
more flexible than Access charts.

Any help would be greatly appreciated!
Thanks in advance,
Marie

 

[Guest]

Why not just use the MSGraph object in Access to create the chart (graph)
that you need?

I use the Chart wizard, which builds the graph based on your specifications
and places it into a form. To get it into a report, simply cut or copy the
graph to the clipboard then paste into your report (I use the ReportFooter
section usually).

Randall Arnold

 

[Marie via AccessMonster.com]

Randall,

I have tried that, but the graphs do not always appear the way you would like
them, e.g. I have a chart which has the month names on the X axis. The query
is sorted correctly, but after going through the wizard and copy and paste,
the month names appear in alphabetical order on the report.
Any ideas on how to fix this?

Many thanks,
Marie

Why not just use the MSGraph object in Access to create the chart (graph)
that you need?

I use the Chart wizard, which builds the graph based on your specifications
and places it into a form. To get it into a report, simply cut or copy the
graph to the clipboard then paste into your report (I use the ReportFooter
section usually).

Randall Arnold

Using Access 2000 I am trying to export Access query results to Excel where
I would like to create a chart. I would then like to import the chart into an

[quoted text clipped - 32 lines]

Thanks in advance,
Marie

 

[Guest]

I had the same problem as you at first. I wound up sorting on the primary
key instead of the text field. This solved the problem for me. Did you
verify that the recordsource of your graph is pointing to the query and not a
table?

I'm not sure exactly how your tables and queries are setup, but in your case
I think making sure there's an associated index where January = 1, February =
2, etc and then sort Ascending on that value would do the trick. You may
have to create a table of Months only, then create a relationship between
that table and your existing table, and then include the months table in your
query for sorting on the month ordinal.

Randall Arnold

Randall,

I have tried that, but the graphs do not always appear the way you would like
them, e.g. I have a chart which has the month names on the X axis. The query
is sorted correctly, but after going through the wizard and copy and paste,
the month names appear in alphabetical order on the report.
Any ideas on how to fix this?

Many thanks,
Marie

Why not just use the MSGraph object in Access to create the chart (graph)
that you need?

I use the Chart wizard, which builds the graph based on your specifications
and places it into a form. To get it into a report, simply cut or copy the
graph to the clipboard then paste into your report (I use the ReportFooter
section usually).

Randall Arnold

Using Access 2000 I am trying to export Access query results to Excel where
I would like to create a chart. I would then like to import the chart into an

[quoted text clipped - 32 lines]

Thanks in advance,
Marie

 

[Guest]

It's searching for a Dim statement for objExcel, I believe.