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/office/forum/office_2013_release-powerpoint/powerpoint-vba-chart-data-sheet/7bd63cc6-f27a-4acf-83f4-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\ (file://\\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