Improve chart refreshing and interface with Access
Ideally, Access should have as good charting available as Excel, and they should both work the same. But despite recent improvements in Access charts, there are some things that it cannot do. I was able to easily link an Excel chart to an Access report, but the problem is that in a typical Access report the chart needs to use different data on each grouping on the report, and I've been unable to find a way to refresh the data and the chart in the linked spreadsheet / chart.
I've tried a different approach, using VBA code to manipulate an Excel instance, and that code works fine for refreshing the data in Excel (from a SQL stored procedure), but I've been unable to find any VBA code that will make the chart refresh. I've used something similar to the following, and can't find any VBA to make the chart refresh:
gObjExcel.Workbooks("TissueSLGraph.xlsx").Connections("SoilTestStu").OLEDBConnection.CommandText = Array("EXEC dbo.TissueSLGraph @Yr = " & CStr(Me.Yr) & ", @FldNo = " & CStr(Me.FieldID) & ", @Crop = " & CStr(Me.TissueCropID) & "")
For Each myChart In gObjExcel.ActiveSheet.ChartObjects
'gObjExcel.Visible = True
'gObjExcel.ActiveChart.FullSeriesCollection(2).Values = "=TissueSLGraph.xlsx!Diff"
I’m stumped, and consider the fact that the Chart.Refresh command does not work an Excel bug. Any help appreciated!
I wish someone from Microsoft would look at this issue. I really feel that it is a bug in the newer versions or Access, but I can't get anyone to look at it.