Consulting

Results 1 to 13 of 13

Thread: Solved: Replace Text in the Datasheet of a Chart

  1. #1

    Solved: Replace Text in the Datasheet of a Chart

    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.

  2. #2
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    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
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

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

    Cheers

  4. #4
    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?

    [vba]If ograph.Application.DataSheet.Range("01").Value = "NEWVALUE" Then[/vba]

    Thank you

  5. #5
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    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!
    [vba]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[/vba]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

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

  7. #7
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    What's the error message?
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  8. #8
    Run-time error '91':

    Object variable or With black variable not set.

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

  9. #9
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    My mistake!

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

    Like this

    [vba]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[/vba]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  10. #10
    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.

  11. #11
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    Don't have 2003 here at the moment to check this but I imagine this would work

    [vba]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
    [/vba]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  12. #12

    Thumbs up

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

  13. #13
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    You're welcome.
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •