CODEWITHSUNDEEP
vbapowerpoint
chairSitter
chairSitter

Reputation: 81

How to paste data from Excel into PowerPoint

i came across some code from the msdn.microsoft.com website, but I am getting an error when trying to test it. This code is literally their example code. Code below:

Sub ReplaceText()

Dim oSld As Slide
Dim oShp As Shape
Dim oTxtRng As TextRange
Dim oTmpRng As TextRange

Set oSld = Application.ActivePresentation.Slides(1)

For Each oShp In oSld.Shapes

    Set oTxtRng = oShp.TextFrame.TextRange
    Set oTmpRng = oTxtRng.Replace(FindWhat:="Name Here", _
        Replacewhat:="TESTTEST", WholeWords:=True)

Next oShp

End Sub

The error occurs in the Set oTxtRng = oShp.TextFrame.TextRange line, and the error is "the specified value is out of range". Anyone know why I am getting this error, and how I can avoid it? This is in the VBA editor in Powerpoint, and I have the ppt presentation open

Upvotes: 0

Views: 86

Answers (2)

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

Some shapes don't have textframes (lines/connectors/OLE objects, etc) so you want to test for that first. And then the shape may have a text frame but no text in it, so you test for that. And THEN you can assign the textrange to a variable:

If oShp.HasTextFrame Then
  If oShp.TextFrame.HasText Then
    Set oTxtRng = oShp.TextFrame.TextRange
  End If
End If

Upvotes: 1

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

If you have ppt already opened, do not create but get object. Instead of:

Set oPPTApp = CreateObject("PowerPoint.Application")

you should have 

Set oPPTApp = GetObject(, "PowerPoint.Application")

Then, don't open presentation, but use this already opened and active:

Set oPPTFile = oPPTApp.ActivePresentation

If it has only one slide, you can access shapes in it with the following code 

Dim sh As PowerPoint.Shape
For Each sh In oPPTFile.Slides(1).Shapes
   'do something with sh
Next sh

The property you are looking for is probably sh.TextFrame.TextRange.Text, but make experiments with Intellisense.

Upvotes: 1

Related Questions