PowerPoint VBA Chart Data sheet

Question

Howdy,

I am trying to loop through all of the charts in a presentation, open the chart sheet, and use the replace function on the contents.

Can someone help me out?

Thanks

Jeff

Answer 1

You probably should set a reference to the Excel 15 Object Library in Tools > References in the vb editor to do this and then

Sub fixEmALL()

Dim ocht As Chart

Dim oshp As Shape

Dim osld As Slide

Dim wb As Excel.Workbook

Dim ws As Excel.Worksheet

Dim strText As String

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:="old", Replacement:="new"

ocht.Refresh

wb.Close

End If

Next oshp

Next osld

End Sub

Answer 2

Thanks for the quick reply!  How would I adapt this for all cells?  I cannot seem to do a global replace.

Answer 3

See if this gets you started

Sub fixEm()

Dim ocht As Chart

Dim oshp As Shape

Dim osld As Slide

Dim strText As String

For Each osld In ActivePresentation.Slides

For Each oshp In osld.Shapes

If oshp.HasChart Then

Set ocht = oshp.Chart

With ocht.ChartData

.Activate

strText = .Workbook.worksheets(1).Range("A2").Value

strText = Replace(strText, "This", "That")

.Workbook.worksheets(1).Range("A2").Value = strText

.Workbook.Close

End With

End If

Next oshp

Nextosld

End Sub