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
HowTo OMA-URI - something not working
PatrickF11 in Microsoft Intune on
19 Replies
Teams Updater Vulnerability
Andrew Matthews in Microsoft Teams on
15 Replies
Pulling data from one tab to another
krysphares in Excel on
6 Replies
vba sending email w/ attachment
katrina bethea in Excel on
7 Replies
VBA - Splitting worksheet by Dept
Zia Siddique in Excel on
12 Replies