Update PPT Template from Excel

Question

Hi - I've spent hours searching for a solution, but I'm only finding "How to update Charts in PPT from Excel".

I have an Excel template and a PPT Template.  The standard template wording in the file is defaulted to "ABC Co.".  For example on the PPT Template, slide 3 has a text box which contains the text:  "Our client, ABC Co, offers the following benefits to the employees:"

I'm looking for a way where an end user can change the Excel file from "ABC Co." to another client (IE: "XYZ Inc.") and have PPT do either of the following;

1.  Replace the "ABC Co." in the slide 3 textbox with "XYZ Inc." or
2.  Replace all instances of "ABC Co." in the entire PPT presentation.

I'm very good with Excel VBA, but not so good with PPT / PPT VBA.  I would like to get some VBA code to accomplish this, and then leverage off this code to make several other similar updates (IE:  Number of Employees, Number of Offices, etc).  All told, there will probably be about 50 updates from Excel over to PPT, but I thought if I could get 1 update, I could then figure out how to do the other 49.

Also, not sure if this will be a problem, but within the text box, there is different font colors and sizes.  In experimenting, I was able to find how to bring over text from Excel, but it changed the entire text box to the font and color of the 1st word.  1st word in the PPT text box is "Benefits" and the font is Calibri / Bold / size 16 / color: Blue.  My text for "ABC Co." is Calibri / 10 / non bold / red.

Any help is greatly appreciated.

thanks,

GRC

Answer 1

They're defined in this line:

Sub TextReplace(sSearchFor As String, sReplaceWith As String)

You can call it multiple times that way:

TextReplace "This", "That"

TextReplace "Something", "Something Else"

TextReplace "Boring ugly text", "Exciting, really cool text"

TextReplace "And so on", "And so forth"

Answer 2

Hi Steve -

thanks for your assistance.  Where do you / did you define the variables 'sSearchFor' and 'sReplaceWith'?

Can those be cells back on an Excel spreadsheet?

thanks,

GRC

Answer 3

If you need an immediate solution that'll do what you want and a good deal more, have a look at my Merge add-in for PowerPoint.  The free demo is completely functional; you can test thoroughly at no cost.

http://www.pptools.com/merge/

But if you want to DIY, this'll get you started; if you need to run it from within Excel, you'll need to modify it a bit, but by using the Replace method as shown, it'll preserve the formatting of any text it replaces.

Sub TextReplace(sSearchFor As String, sReplaceWith As String)

    Dim oSl As Slide

    Dim oSh As Shape

    With ActivePresentation

        For Each oSl In .Slides

            For Each oSh In oSl.Shapes

                With oSh

                    If .HasTextFrame Then

                        If .TextFrame.HasText Then

                            .TextFrame.TextRange.Replace sSearchFor, sReplaceWith

                        End If

                    End If

                End With

            Next

        Next

    End With

End Sub