none
Can't get Excel chart to refresh using VBA code RRS feed

  • Question

  • I'm using VBA code in an Access report 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) & "")

    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 can refresh the chart manually either by saving and reopening the spreadsheet or by clicking on a series and starting to edit the data location.  But no code I try will do it sucessfully

    I’m stumped, and consider the fact that the Chart.Refresh command does not work an Excel bug. Any help appreciated!

    Saturday, April 20, 2019 2:47 PM

All replies

  • Hi,
    Regarding the information about VBA code, I am sorry that it has exceeded the scope of support here, and this may not help you.

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel
    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Bella Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.


    Monday, April 22, 2019 8:03 AM