Creating charts in Excel

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

This is a pretty in depth question so I hope that someone
can help.

Due to the limitations of the chart creation abilities of
Access, I need to be able to export data to an excel
workbook, then have excel create a chart which is linked
to an Access report. That part I already have, my problem
is that I am going to be distributing this application to
a lot of users in the near future and now run the risk of
more than one person creating this chart at on time (which
cant happen since the data is being exported from Access
to only 1 specific Excel file)

So... I want to split my database into a front and back
end and then have the front end open up an Excel workbook
and the users' machine, then run an excel macro to create
a chart which can then be viewed as an Access report. This
is where my questions start.

I found some code to open up an instance of excell (in
hidden mode) and run an excel macro at
http://www.mvps.org/access/modules/mdl0007.htm.

My questions are:

1. Is there a way to export data to the active workbook?
If so how would I specify it?

2. How would I either import the chart from excell to
Access from the active workbook or at least reference it
in a way that I would be able to view it in a report?

I know this may be kind of confusing but please let me
know of any advice. Thanks.

Chad
 
Hi Chad,

1) Controlling Excel from Access and passing data: see the following
links, and also check out Excel's Range.CopyFromRecordset method.

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235


2) I don't know whether this is possible. I'd try quite hard to look for
another way of achieving the output I wanted. But if pressed I'd
investigate using an unbound ObjectFrame control linked to the Excel
workbook.
 
Back
Top