Improve Access charting, or improve the interaction with Excel
I have a need to place a fairly complex chart in an Access report, and I wasn't able to create the chart in Access so I did it with Excel. The data actually resides in SQL Server, and there is a data connection that runs a stored procedure to bring it into Excel. This works find if you manually refresh the data in Excel, get the chart to refresh in Excel (which doesn't seem to work as easily as it should), and save the spreadsheet. If you then run the report the linked chart will work correctly. But when you are working with a database, a more typical scenario would be that you need to repeat the chart with different data for each group header or footer, so you may need to produce hundreds of them, and that has been a nightmare for me. I have been unable to discover a good method of automating the refreshing of both the data and the chart, and getting it from Excel to the Access report. Doesn't seem like this should be so difficult. For that matter, it would seem to make sense for charting to be the same in Access and Excel.
Dick Schroth commented
I just can't seem to find code that will reliably refresh the chart. I can do it manually by clicking on a line, choosing "Select Data" and as soon as I click on one of the data ranges the chart refreshes.