Export Excel Chart to Access Report Automatically (1 Viewer)

[ray147]

I am currently creating an automated process to export an Access query data to Excel, create a pivot table and pivot chart and update an Access report. Till now I managed to get to the point where in Excel I have the Pivot Chart exactly as I require. All I need now is to copy that chart and paste it into the Access report in the 'Detail' section, automatically of course. I know how to copy the chart in Excel through VBA, but dunno how I can code the pasting into the Access report.

I am assuming that I need to open the report in design view using the "DoCmd.OpenForm "Compliance_Report", acDesign" command but I can't figure out how to do the 'paste'.

Any help greatly appreciated of course!

Thanks

 

[ray147]

is this possible at all guys?

 

[richary]

Well, I don't know if there are other ways which are cleaner but in Excel you can use the Charts .Export method to save the chart as a.GIF (or .JPG if you prefer) formatted file.

In Access report design, insert an object from a file making sure the link to file checkbox is checked.

Just make sure that everytime you update the chart in Excel, you export the file and any changes shoudl be reflected in your Access report.

PS No need to paste the chart everytime with this method, just open the report in acPreview mode.