Importing an Excel chart to an Access report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marie - 21 Sep 2005 14:33 GMT

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

Reply to this Message

Randall Arnold - 21 Sep 2005 15:58 GMT

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

Reply to this Message

Marie - 21 Sep 2005 18:07 GMT

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?
[quoted text clipped - 11 lines]
>> Thanks in advance,
>> Marie

Reply to this Message

Randall Arnold - 21 Sep 2005 19:36 GMT

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,
>
[quoted text clipped - 22 lines]
> >> Thanks in advance,
> >> Marie

Reply to this Message

Carol Grismore - 20 Dec 2005 18:22 GMT

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

Signature

Carol

Reply to this Message