Change data in Chart Sheet

[jmenche]

Howdy,

I am trying to loop through charts in PowerPoint and change the data. The charts are NOT Excel embedded or linked but native PowerPoint charts. I get as far as chartdata.activate and then get stuck. I want to do a find/replace on the chart sheet. Does anyone know how I can do that?

Thanks

[John Wilson]

This is probably a cross post and the other post has an answer.

http://answers.microsoft.com/en-us/o...4-368867aebff7

[jmenche]

John,

Thanks. This technically works BUT it is really slowwwwww. For some reason, I cannot attach a file but here's the deal. We crank out 60+ page decks for our clients. Most of the slides have charts. Someone thought it would be a great idea to place a network reference instead of hard data in the chartdata. Now, I have to change the network path. When I watch the macro work, the replace function loops through each cell in the chartdata individually instead of all at once (like in Excel). It takes a couple of minutes to update a chart and I have multiple decks to update. I am not sure if it is stopping to retrieve each cell's new data. Is there a setting that I can turn off to speed this process up?

I appreciate any help.

Jeff

Sub ChangePath1()
    Dim ocht As Chart
    Dim oshp As Shape
    Dim osld As Slide
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim strText, strOldPath, strNewPath As String
    
    strOldPath = "N:\"
    strNewPath = "\\garw-fp01\clients\"
    
    For Each osld In ActivePresentation.Slides
        For Each oshp In osld.Shapes
            If oshp.HasChart Then
                Set ocht = oshp.Chart
                ocht.ChartData.Activate
                Set wb = ocht.ChartData.Workbook
                Set ws = wb.Worksheets(1)
                ws.Cells.Replace What:=strOldPath, Replacement:=strNewPath
                ocht.Refresh
                wb.Close
            End If
        Next oshp
    Next osld
End Sub