Home

Unable to update chart using VBA when data is updated

%3CLINGO-SUB%20id%3D%22lingo-sub-401119%22%20slang%3D%22en-US%22%3EUnable%20to%20update%20chart%20using%20VBA%20when%20data%20is%20updated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401119%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22x_MsoNormal%22%3EI%20have%20a%20spreadsheet%20with%20a%20chart%20that%20I%20can't%20get%20to%20refresh%20using%20code.%26nbsp%3B%20The%20data%20comes%20from%20a%20SQL%20Server%20stored%20procedure%2C%20and%20I%E2%80%99m%20actually%20using%20the%20chart%20on%20an%20Access%20report%2C%20which%20requires%20both%20the%20data%20and%20the%20chart%20to%20be%20refreshed%20on%20each%20page%20of%20the%20report.%26nbsp%3B%20I%20have%20attached%20an%20image%20of%20the%20chart%20in%20an%20unrefreshed%20state%2C%20after%20the%20data%20has%20been%20refreshed%2C%20and%20if%20you%20look%20closely%20you%20will%20see%20that%20the%20X%20axis%20categories%20do%20not%20match%20the%20%22Stage%22%20in%20the%20table.%26nbsp%3B%20My%20VBA%20code%20refreshes%20the%20data%20fine.%26nbsp%3B%20I%20am%20manipulating%20the%20Excel%20instance%20from%20within%20Access%20using%20variations%20of%20the%20code%20below.%26nbsp%3B%20By%20making%20the%20Excel%20object%20visible%20I%20can%20see%20the%20effect%20of%20each%20command%2C%20and%20the%20data%20refreshes%20fine.%26nbsp%3B%20But%20I%20have%20been%20unable%20to%20find%20a%20VBA%20command%20that%20will%20successfully%20refresh%20the%20graph%2C%20and%20I%E2%80%99ve%20tried%20everything%20I%20can%20think%20of.%26nbsp%3B%20Manually%2C%20I%20can%20click%20on%20a%20line%20on%20the%20chart%2C%20choose%20%E2%80%9CSelect%20Data%E2%80%9D%2C%20and%20as%20soon%20as%20I%20click%20on%20one%20of%20the%20ranges%20the%20chart%20is%20miraculously%20refreshed.%26nbsp%3B%20But%20trying%20to%20do%20the%20same%20thing%20in%20code%20does%20nothing.%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3EgObjExcel.Workbooks(%22TissueSLGraph.xlsx%22).Connections(%22SoilTestStu%22).OLEDBConnection.CommandText%20%3D%20Array(%22EXEC%20dbo.TissueSLGraph%20%40Yr%20%3D%20%22%20%26amp%3B%20CStr(Me.Yr)%20%26amp%3B%20%22%2C%20%40FldNo%20%3D%20%22%20%26amp%3B%20CStr(Me.FieldID)%20%26amp%3B%20%22%2C%20%40Crop%20%3D%20%22%20%26amp%3B%20CStr(Me.TissueCropID)%20%26amp%3B%20%22%22)%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20gObjExcel.Workbooks(%22TissueSLGraph.xlsx%22).Connections(%22SoilTestStu%22).Refresh%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20gObjExcel.Workbooks(%22TissueSLGraph.xlsx%22).Sheets(%22Graph%22).Select%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFor%20Each%20myChart%20In%20gObjExcel.ActiveSheet.ChartObjects%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20myChart.Chart.Refresh%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B'gObjExcel.Visible%20%3D%20True%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BgObjExcel.ActiveSheet.ChartObjects(%22Chart%201%22).Activate%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B'gObjExcel.ActiveChart.FullSeriesCollection(2).Select%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'gObjExcel.ActiveChart.FullSeriesCollection(2).Values%20%3D%20%22%3DTissueSLGraph.xlsx!Diff%22%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3EI%E2%80%99m%20stumped%2C%20and%20consider%20the%20fact%20that%20the%20Chart.Refresh%20command%20does%20not%20work%20an%20Excel%20bug.%26nbsp%3B%20Any%20help%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-401119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
D_Schroth
Occasional Visitor

I have a spreadsheet with a chart that I can't get to refresh using code.  The data comes from a SQL Server stored procedure, and I’m actually using the chart on an Access report, which requires both the data and the chart to be refreshed on each page of the report.  I have attached an image of the chart in an unrefreshed state, after the data has been refreshed, and if you look closely you will see that the X axis categories do not match the "Stage" in the table.  My VBA code refreshes the data fine.  I am manipulating the Excel instance from within Access using variations of the code below.  By making the Excel object visible I can see the effect of each command, and the data refreshes fine.  But I have been unable to find a VBA command that will successfully refresh the graph, and I’ve tried everything I can think of.  Manually, I can click on a line on the chart, choose “Select Data”, and as soon as I click on one of the ranges the chart is miraculously refreshed.  But trying to do the same thing in code does nothing.

 

gObjExcel.Workbooks("TissueSLGraph.xlsx").Connections("SoilTestStu").OLEDBConnection.CommandText = Array("EXEC dbo.TissueSLGraph @Yr = " & CStr(Me.Yr) & ", @FldNo = " & CStr(Me.FieldID) & ", @Crop = " & CStr(Me.TissueCropID) & "")

       gObjExcel.Workbooks("TissueSLGraph.xlsx").Connections("SoilTestStu").Refresh

       gObjExcel.Workbooks("TissueSLGraph.xlsx").Sheets("Graph").Select

      

       For Each myChart In gObjExcel.ActiveSheet.ChartObjects

           myChart.Chart.Refresh

       Next

       'gObjExcel.Visible = True

      

       gObjExcel.ActiveSheet.ChartObjects("Chart 1").Activate

      

       'gObjExcel.ActiveChart.FullSeriesCollection(2).Select

       '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!

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies