none
Refreshing chart data RRS feed

  • Question

  • I have time series ppt charts where the associated workbook "chart in microsoft powerpoint" is linked to an external workbook. As I update the external workbook the new data feeds through into the ppt chart when I open "chart in microsoft powerpoint" with edit data.

    I have to do this for each chart in the deck to update the complete set, which I do 13 times per annum for 50 or so chart decks, each deck contains around 20 charts, so it's a lot of edit data /close etc....

    Can I do this automatically? Is there VBA code that would do this?

    Regards

    Tim

    Friday, October 9, 2015 10:40 AM

Answers

  • Hi Tim,

    We can using ChartData.Activate method to activate the first window of the workbook associated with the chart. After the windows display, the chart will update based on the data then we can close the workbook.

    Here is an example for your reference:

    Sub RefreshChart()
    Dim aSlide As Slide
    Dim aShape As Shape
    Dim aChart As Chart
    For Each aSlide In Application.ActivePresentation.Slides
        For Each aShape In aSlide.Shapes
            If aShape.HasChart Then
                aShape.Chart.ChartData.Activate
                aShape.Chart.ChartData.Workbook.Close
    
            End If
        Next aShape
    Next aSlide
    End Sub
    
    
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 12, 2015 9:40 AM
    Moderator

All replies

  • Hi Tim,

    We can using ChartData.Activate method to activate the first window of the workbook associated with the chart. After the windows display, the chart will update based on the data then we can close the workbook.

    Here is an example for your reference:

    Sub RefreshChart()
    Dim aSlide As Slide
    Dim aShape As Shape
    Dim aChart As Chart
    For Each aSlide In Application.ActivePresentation.Slides
        For Each aShape In aSlide.Shapes
            If aShape.HasChart Then
                aShape.Chart.ChartData.Activate
                aShape.Chart.ChartData.Workbook.Close
    
            End If
        Next aShape
    Next aSlide
    End Sub
    
    
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 12, 2015 9:40 AM
    Moderator
  • That sounds great Fei, I shall try it now. Thank you very much!!

    Regards

    Tim

    Tuesday, October 27, 2015 10:00 AM
  • Hi Fei,

    this is certainly getting there. I have tried it lots of times but it won't run the whole way through the deck and seems to stop randomly and crashes excel. I am getting these errors.

    run time 432. file name or class name not found during automation operation

    and

    run time -2147467259 (80004005).  Method 'workbook' of object chartdata failed

    It's so close. The charts it's stopping on are all essentially the same or very similar formats. Does it matter if there are several charts in one slide? It doesn't seem to but then again it might?

    It would be great if you could help me to make this work, I really do appreciate it.

    Regards

    Tim

    Tuesday, October 27, 2015 11:01 AM
  • Hi Fei,

    I have been testing this a lot. It's frustrating because sometimes it works perfectly and then the next time it will stop and crash excel. The most frequent place it seems to stop is at closing the workbook but sometimes on activating the chart even if there is one on the slide. Sometimes, after it's crashed, only excel crashes not ppt, one of the charts not will no longer 'edit', there doesn't seem to be a workbook in the shape any more. Weird? Any ideas?

    Cheers

    Tim

    Wednesday, October 28, 2015 1:03 PM
  • Hi Fei,

    could the crashes be some sort of caching issue?

    Regards

    Tim

    Wednesday, October 28, 2015 3:06 PM
  • Hi Fei,

    I have started to run your procedure on a new computer and it works perfectly!!!

    Something in my old PC didn't agree with it.

    Can't thank you enough!!! It will save me a lot of time.

    Regards

    Tim

    Thursday, November 5, 2015 10:57 AM