MS Access and Excel Charts

[puforee]

My Access DB (2003) calculates data needed for charting. I use MS Excel (2003)and a DB Query to build the chart in Excel.

In a MS Access Report object I link to the Excel Charts so they can be shown in Access.

Issue. When I update data in MS Access the Excel Chart does Not pick up the new data until it is opened.

Question. Is there any way, macro/code, for MS Access to trigger the update to the excel spreadsheet? I want my users only to have to use MS Access to see the charts...but excel must be updated, after every data updae, to show the current data.

Thanks,

 

[dhookom]

I expect you could use automation to open Excel and requery the charts. I have only used Access charts in Access reports.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Automation?

I did find information by clicking the More Like This...above. "flize" showed code that will open and close a spreadsheet. I think I can open and close the spreadsheet now. The data in the spreadsheet is set to update automatically when it is opened...so I think that may help.

I am using an older version of Access and the charting capability is lousy. So, I went with Excel to get what I wanted. I find I can control ledgens or formating of the Access created Charts.

Thanks for the feed back.

 

[dhookom]

If you have any questions about Excel Automation, the best place to ask is in forum707. There are some very sharp programmers reading through the posts there.

Duane
Hook'D on Access
MS Access MVP