Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Variable codes in ppt from excel (Office 2003)

    I want to search through a sheet in Excel from A1 to B50. In Column A are the "codes" that are in powerpoint. In Column B is the text that needs to go in place of the "codes" in powerpt.

    So A1 is [country], A2 is [year], A3 is [color]
    B1 is Canada, B2 is 2008, B3 is Red

    The codes [country]. [year] and [color] are in a powerpt file of 100 slides. I need the code to go through the ppt file and replace the codes with the text in Column B.

    I have this code but don't know exactly how to change it to fit my new excel sheet setup. Can someone help me? Thank you very much.


    Sub PPTfind_replace()

    Dim sld As PowerPoint.Slide
    Dim shp As PowerPoint.Shape
    Dim i As Integer
    Dim code As String
    Dim pptApp As PowerPoint.Application
    Dim xlwsText As Excel.Worksheet
    Dim myArray(14 To 22) As String

    Set xlwsText = ActiveSheet
    Set pptApp = GetObject(, "PowerPoint.Application")

    ' fill array with first row in Excel
    For i = 1 To 50
    code = "[" & i & "]"
    myArray(i) = xlwsText.Cells(i, 23)
    For Each sld In pptApp.ActivePresentation.Slides
    For Each shp In sld.Shapes
    If shp.HasTextFrame Then _
    shp.TextFrame.TextRange.Replace _
    FindWhat:=code, ReplaceWhat:=myArray(i)
    Next shp
    Next sld
    Next i
    Set xlwsText = Nothing
    Set pptApp = Nothing
    MsgBox ("FindReplace is Finished")
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable codes in ppt from excel (Office 2003)

    Try this:

    Sub PPTfind_replace()
    Dim sld As PowerPoint.Slide
    Dim shp As PowerPoint.Shape
    Dim i As Integer
    Dim code As String
    Dim theval As String
    Dim pptApp As PowerPoint.Application
    Dim xlwsText As Worksheet

    Set xlwsText = ActiveSheet
    Set pptApp = GetObject(, "PowerPoint.Application")

    For i = 1 To 50
    code = xlwsText.Cells(i, 1)
    theval = xlwsText.Cells(i, 2)
    For Each sld In pptApp.ActivePresentation.Slides
    For Each shp In sld.Shapes
    If shp.HasTextFrame Then _
    shp.TextFrame.TextRange.Replace _
    FindWhat:=code, ReplaceWhat:=theval
    Next shp
    Next sld
    Next i
    Set xlwsText = Nothing
    Set pptApp = Nothing
    MsgBox "FindReplace is Finished"
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variable codes in ppt from excel (Office 2003)

    This is working great! Is there a way to keep the same number formatting when it puts the numbers in the ppt file? Some are whole numbers, some are percents and it's putting them in a decimals. Is there a way to copy the formatting so whatever i format in Excel will carry over to ppt. Thanks a lot.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable codes in ppt from excel (Office 2003)

    Sorry for the delay, I was away for some days. Try changing the line

    theval = xlwsText.Cells(i, 2)

    to

    theval = xlwsText.Cells(i, 2).Text

    The Text property of a cell returns the value of the cell as it is displayed.

Posting Permissions

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