Solved: How do I replace text in a table?

[Paul_Hossler]

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 ???

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 

VBA tags courtesy of www.thecodenet.com

Any help please???

Paul

[John Wilson]

Hi Paul
AFAIK the only way is looping through rows and columns.

[Paul_Hossler]

Thanks John -- I'll stop looking.

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

It just just seems very brute force (inelegant?)

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 

VBA tags courtesy of www.thecodenet.com

Paul

[Paul_Hossler]

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

Set oTemp = oText.Replace(BeforeText, AfterText, , WholeWords, MatchCase 

VBA tags courtesy of www.thecodenet.com

Paul

[John Wilson]

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:

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 

VBA tags courtesy of www.thecodenet.com

[Paul_Hossler]

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