Microsoft Excel Webinar

Results 1 to 6 of 6

Thread: Solved: How do I replace text in a table?

  1. #1

    Solved: How do I replace text in a table?

    Working a PP 2010 project to apply some cleanup and reformatting to a PP table that is pasted in from an Excel pivot table.

    Management is more PP oriented that Excel, so we'll do the number crunching with Excel, summarize with a pivot table and then paste it onto a PP slide.

    Bunch of things are applied (so I'll probably be back) but I can't figure out how to do a find and replace to the text in the cells in the table.

    Doing it a cell at a time by looping the rows and looping the columns is taking too long.

    This doesn't seem to work since it looks like a Table (or the shape) doesn't have a TextFrame ???


    VB:
    Option Explicit 
     
    Sub TestThings() 
        Dim oPres As Presentation 
        Dim oSlide As Slide 
        Dim oShape As Shape 
        Dim oTable As Table 
        Dim i As Long 
        Set oPres = ActivePresentation 
        If oPres Is Nothing Then Exit Sub 
         
        Set oSlide = CurrentSlide 
        If oSlide Is Nothing Then Exit Sub 
        For Each oShape In oSlide.Shapes 
            If oShape.HasTable Then 
                 
                Set oTable = oShape.Table 
                 
                If oShape.HasTextFrame Then 
                     
                    If oShape.TextFrame.HasText Then 
                         
                        Call oShape.TextFrame.TextRange.Replace(" Total", vbNullString, , msoTrue, msoFalse) 
                        Call oShape.TextFrame.TextRange.Replace("Sum of ", vbNullString, , msoTrue, msoFalse) 
                        Call oShape.TextFrame.TextRange.Replace("Grand Total", "Total", , msoTrue, msoFalse) 
                        Call oShape.TextFrame.TextRange.Replace("(blank)", vbNullString, , msoTrue, msoTrue) 
                    End If 
                     
                     
                    Stop 
                     
                End If 
                 
            End If 
        Next 
    End Sub 
    
    
    Formatting tags added by mark007

    Any help please???

    Paul

  2. #2
    Hi Paul
    AFAIK the only way is looping through rows and columns.
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  3. #3
    Thanks John -- I'll stop looking.

    BTW, this is the sub that I ended up using.

    It just just seems very brute force (inelegant?)

    VB:
    Private Sub Table_Find_Replace(T As Table, BeforeText As String, Optional AfterText As String = vbNullString, _ 
        Optional WholeWords As MsoTriState = False, Optional MatchCase As MsoTriState = False) 
         
        Dim iRow As Long, iCol As Long 
        Dim oText As TextRange, oTemp As TextRange 
         
        With T 
            For iRow = 1 To .Rows.Count 
                For iCol = 1 To .Columns.Count 
                     
                    Set oText = .Cell(iRow, iCol).Shape.TextFrame.TextRange 
                    Set oTemp = oText.Replace(BeforeText, AfterText, , WholeWords, MatchCase) 
                     
                Next iCol 
            Next iRow 
        End With 
    End Sub 
    
    
    Formatting tags added by mark007
    Paul

  4. #4
    Also, this set just seems to be a throw-away, since nothing is done with it


    VB:
    [COLOR=#0000ff]Set[/COLOR] oTemp = oText.Replace(BeforeText, AfterText, , WholeWords, MatchCase 
    
    
    Formatting tags added by mark007
    Paul

  5. #5
    Hi (again) Paul
    I think you'll find only the first occurance in a cell is replaced? If the word is repeated the other occurrances won't change.

    May not matter in your situation but I think you could use oTemp like this:

    VB:
    Private Sub Table_Find_Replace(T As Table, BeforeText As String, Optional AfterText As String = vbNullString, _ 
        Optional WholeWords As MsoTriState = False, Optional MatchCase As MsoTriState = False) 
         
        Dim iRow As Long, iCol As Long 
        Dim oText As TextRange, oTemp As TextRange 
         
        With T 
            For iRow = 1 To .Rows.Count 
                For iCol = 1 To .Columns.Count 
                    Set oText = .Cell(iRow, iCol).Shape.TextFrame.TextRange 
                    Do 
                        Set oTemp = oText.Replace(BeforeText, AfterText, , WholeWords, MatchCase) 
                    Loop While Not oTemp Is Nothing 
                Next iCol 
            Next iRow 
        End With 
    End Sub 
    
    
    Formatting tags added by mark007
    Last edited by John Wilson; 04-15-2012 at 02:23 AM.
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  6. #6
    Thanks (again) John --

    That makes perfect sense

    Since I plan to use my little sub in places other than a single table cell, my way would have caused problems

    Paul

Posting Permissions

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