How to retain graph from report

[sargum]

Hi,

I am able to successfully generate graph in a report based on some x
and y values and show the raw data on this report as well. I have
since applied a macro that exports this report into Excel. However, it
does not retain the graph that was generated in the report. I know I
know, as long as it exports the raw data, I should be able to make my
own graph in Excel but people are busy and they would want this fully
automated. How can I have Excel or Acess regernate this graph
automatically when raw data is opened in Excel? Any suggestions?

S

 

[SA]

If the data you have is in a prescribed range, i.e. its alwasy X rows by Y
columns then you can create an XL template file and export the data to that
file and have it pick up the cell data to feed the chart. If not, then
you'd have to use VB Automation to recreate the graph in XL via code after
the data is exported.

 

[sargum]

Hi,

Thanks for you email. The data does in fact just have x and y values
but the quantity may be different with every report. How do I creat an
XL template file and feed it X and Y values? Do I create it in MS
Access? I am not sure how to proceed??

Thx.

S

 

[SA]

Look at the OutputTo method. You can pass it the name of an existing XL
file. It will always fill the data rows and columns starting with A1 in an
Excel sheet that has the same name as the query you are using in Access in
the target workbook. If you build your chart on a sheet that you've named
with name of your query or on aother sheet and reference the cells that will
be filled, then you should be able to create the outcome you want with a
template like that.