Help!! on converting Access Graphs/Charts

  • Thread starter Thread starter Zohra
  • Start date Start date
Z

Zohra

Hi there=)

Basically im working on a database system designed to
capture data via the form. Its got many tables related as
well as Queries, Reports and reports in the form of Graphs.

At the end of every month a report must be
produced/printed in Word format for management to review.

Can someone please tell me how to get a chart(actually
several) in Acess format which are based/linked to several
tables/queries into a word Format(if possible) else into
Excel format.

The problem is that the graphs in the new Word/Excel
format should be dynamic..ie: whatever changes take place
in the database system/records should automatically
reflect on the converted graphs in Word/Excel.

I have tried it using Excel by menu..Data-Get External
Data-New Database Qquery and bla bla wizard. The problem
though is that when it comes to the end (Finish- which is
suppose to return it to Excel) it asks for parameters..i
go back to Acess but from that poin im stuck!

Now these parameters are in Access. they are actually dates
(start and End dates) The graphs that are produced are for
the specified time periods.

Other options like analyze with Excel,publish with word
are of no use.

Your help will be greatly appreciated.

Thanks,
Cheers Zohra (South Africa)
 
I actually had to do something exactly like this a few years ago. A set of
weekly charts were required by management to track Quality Control.

The chart object in the version of Access I was using ('97) really sucked,
and the Excel chart object worked pretty good. So, I used Access as the
Database, and Excel as the chart printer. I don't have the code anymore (I
don't work there anymore), but what I did was:

In Excel,
1. Create the graphs in Excel with properties set to look for the data in a
set of cells.
2. Write macros in Excel to parse the data area to find the size of the data
and the date range. (This will change every week as you add the current
week's data to the tables in Access.)
3. The macros would also update the graph properties to match the current
data.
4. The macros would then print the graphs out.

On the Access Side,
1. Write a VB function to create an Excel object, open the file with the
graphs you created, and export the data to the correct cells in the correct
spreadsheets (with the corresponding graphs).
2. Have the VB function run the Excel Macro. Excel will take it from there,
parsing the new data, updating the chart properties, and printing.

This worked great for me. There may be an easier way, but this does work.
You can use Access as the front end, and Excel will run in the background.
This makes it a one-step task rather than having to export the data and then
open Excel and do the printing by hand.

Ross Cox
 
Back
Top