Solved: Replace Text in the Datasheet of a Chart

[jacksonworld]

Hi,

Does anyone know how to replace the text in the datasheet of a chart? I searched the forum and knowledge base to no avail.

As an example, let's say it has "1st Qtr", "2nd Qtr", and "3rd Qrt" in the datasheet. I would like to replace "Qtr" with "Quarter".

I would be grateful if anyone can assist.

Thank you.

[John Wilson]

Not clear which version you are working with. This info is for pre 2007 versions that use msgraph
http://www.pptalchemy.co.uk/MSGraph_vba.html

[jacksonworld]

Thanks. That link is very useful. I will have a play and revert.

Cheers

[jacksonworld]

OK. I have had a play, but I am used to Excel; the syntax required for Powerpoint is foreign to me.

How do I replace all instances of a particular word, irrespective of where it is located in the datasheet?

Is there some way I can edit the following line, or do I need something else?

If ograph.Application.DataSheet.Range("01").Value = "NEWVALUE" Then

Thank you

[John Wilson]

Try this as a starting point - It only replaces text in the 1st row

To search the whole sheet comment in the extra loop.

Note blank cells will kill it!
Sub ograph()
Dim osld As Slide
Dim oshp As Shape
Dim ograph As Object
Dim Icol As Integer
Dim Irow As Integer
Dim strReplace As String
Dim strWith As String
For Each osld In ActivePresentation.Slides
'Find the msGraph object
For Each oshp In osld.Shapes
If oshp.Type = msoEmbeddedOLEObject Then
If oshp.OLEFormat.ProgID Like "MSGraph*" Then
'it's a graph
Set ograph = oshp.OLEFormat.Object
'do something with it
End If 'it's an OLE object
End If 'it's a graph
Irow = 1
Icol = 2 ' avoids blank cell at 1,1
strReplace = "Qtr"
strWith = "Quarter"
'Do While ograph.Application.DataSheet.Cells(Irow, Icol) <> ""
Do While ograph.Application.DataSheet.Cells(Irow, Icol) <> ""
ograph.Application.DataSheet.Cells(Irow, Icol) = _
Replace(ograph.Application.DataSheet.Cells(Irow, Icol), strReplace, strWith)
Icol = Icol + 1
Loop
'Icol = 1
'Irow = Irow + 1
'Loop
Next oshp
Next osld
End Sub

[jacksonworld]

Hmm. I am receiving an error with the first line. Very odd.

[John Wilson]

What's the error message?

[jacksonworld]

Run-time error '91':

Object variable or With black variable not set.

When I debug, the following line is highlighted: Do While ograph.Application.DataSheet.Cells(Irow, Icol) <> ""

[John Wilson]

My mistake!

The search loop needs to be INSIDE the If / End Ifs

Like this

Sub ograph()
Dim osld As Slide
Dim oshp As Shape
Dim ograph As Object
Dim Icol As Integer
Dim Irow As Integer
Dim strReplace As String
Dim strWith As String
For Each osld In ActivePresentation.Slides
'Find the msGraph object
For Each oshp In osld.Shapes
If oshp.Type = msoEmbeddedOLEObject Then
If oshp.OLEFormat.ProgID Like "MSGraph*" Then
'it's a graph
Set ograph = oshp.OLEFormat.Object
'do something with it
Irow = 1
Icol = 2 ' avoids blank cell at 1,1
strReplace = "Qtr"
strWith = "Quarter"
'Do While ograph.Application.DataSheet.Cells(Irow, Icol) <> ""
Do While ograph.Application.DataSheet.Cells(Irow, Icol) <> ""
ograph.Application.DataSheet.Cells(Irow, Icol) = _
Replace(ograph.Application.DataSheet.Cells(Irow, Icol), strReplace, strWith)
Icol = Icol + 1
Loop
'Icol = 1
'Irow = Irow + 1
'Loop
End If 'it's an OLE object
End If 'it's a graph
Next oshp
Next osld
End Sub

[jacksonworld]

Sorry. I did not receive an email notifying me of a reply, so I just checked this.

That works absolutely perfectly for replacing text in the first row, so thank you very much for that.

I tried changing the code so that it works for all cells in the datasheet, but it is not working. Do you mind clarifying what to change, as I am obviously doing it incorrectly.

Thanks once again.

[John Wilson]

Don't have 2003 here at the moment to check this but I imagine this would work

Sub ograph()
Dim osld As Slide
Dim oshp As Shape
Dim ograph As Object
Dim Icol As Integer
Dim Irow As Integer
Dim strReplace As String
Dim strWith As String
For Each osld In ActivePresentation.Slides
'Find the msGraph object
For Each oshp In osld.Shapes
If oshp.Type = msoEmbeddedOLEObject Then
If oshp.OLEFormat.ProgID Like "MSGraph*" Then
'it's a graph
Set ograph = oshp.OLEFormat.Object
'do something with it
Irow = 1
Icol = 2 ' avoids blank cell at 1,1
strReplace = "Qtr"
strWith = "Quarter"
Do While ograph.Application.DataSheet.Cells(Irow, Icol) <> ""
Do While ograph.Application.DataSheet.Cells(Irow, Icol) <> ""
ograph.Application.DataSheet.Cells(Irow, Icol) = _
Replace(ograph.Application.DataSheet.Cells(Irow, Icol), strReplace, strWith)
Icol = Icol + 1
Loop
Icol = 1
Irow = Irow + 1
Loop
End If 'it's an OLE object
End If 'it's a graph
Next oshp
Next osld
End Sub

[jacksonworld]

That works perfectly. Thank you so much. I have so many uses for this. I can't wait to get started. :thumb

[John Wilson]

You're welcome.