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

    Control ppt from Excel (Excel/PPT xp)

    I am trying to write code to control powerpoint from Excel. I have enabled the reference to powerpoint in Excel.

    Here is the code...
    Sub PPTfind_replace()

    Dim sld As Slide
    Dim shp As Shape
    Dim i As Integer
    Dim txtRng As TextRange
    Dim foundText As TextRange
    Dim replaceText As TextRange
    Dim code As String
    Dim pptApp As PowerPoint.Application
    Dim xlApp As Excel.Application
    Dim xlwbDataTemp As Object
    Dim xlwbSnap_ppt As Object
    Dim xlwsText As Object
    Dim myArray(14 To 22)
    Dim intCell As Integer
    Dim market As String

    Set D3 = Workbooks("demo3.xls")
    Set xlwsText = Workbooks("demo3.xls").Sheets("Input")
    Set pptApp = GetObject(, "PowerPoint.Application")

    ' fill array with first row in Excel
    i = 0
    For i = 14 To 22
    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
    Set txtRng = shp.TextFrame.TextRange
    Set foundText = txtRng.Find(FindWhat:=code)
    Set replaceText = txtRng.Replace(FindWhat:=code, ReplaceWhat:=myArray(i))
    End If
    Next shp
    Next sld
    Next i
    MsgBox ("FindReplace is Finished")

    End Sub

    I have text in cells W14 to W22. I want to replace "codes" I've put in demo3.ppt such as <<1>>, <<2>>, <<3>> etc. So W14's text will go in <<1>> and W15's text will go in <<2>>.

    When I use the code it bombs at

    For Each shp In sld.Shapes

    and I don't know why. I want it to look through each slide in demo3.ppt, go through each shape and find the <<code>> and replace it with the proper text from the excel file demo3.xls.

    Thank you for the help. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control ppt from Excel (Excel/PPT xp)

    The problem is that both PowerPoint and Excel have a Shape object which are totally different. Since you were running from Excel and didn't qualify Shape in the dim statement, Excel assumed that it was an Excel shape. Whenever you are using two Office products, you are safest to qualify each object, so you code should look like: (note, I removed anything that wasn't being used in your code snippet just to make it easier for me to understand) HTH --Sam
    <pre>Option Explicit

    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 = 14 To 22
    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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control ppt from Excel (Excel/PPT xp)

    I knew that you code looked familiar! I just found almost the same code on my hard drive. I would post it, but it doesn
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Control ppt from Excel (Excel/PPT xp)

    Thank you very much. This works great now. Do you have any ideas about another question i have? Post: 198302.

Posting Permissions

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