Pass text with formatting as variable from file to file using vba

Question

I'm relatively new to VBA and have only very limited programming experience in general, but would really appreciate some help!

The ultimate goal is to pass (formatted) text from a text box in PPT as a variable between presentations. I believe it is important that the (formatted) text be passed as a variable, because the variable will be used to generate the body of an email (that part of the code is done, but I'm trying to create the guts of that variable here). Unfortunately, I have no idea how to pass a variable in VBA. I think I've figured out how to grab the text, but the simple formatting (bold, text size differences, etc) is lost. Help please? :-)

Dim headlines headlines = ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.Text

The issue I see is that variables don't take in formatted text. They are 'strings' or raw unformatted text. The easiest way would be using the Copy/Paste command piggy backing off of the Clipboard to preserve all of the formatting. If you're using VBA in outlook you can just perform a .Paste command on the — Kris B, Nov 12 '15 at 22:46
(2/2) email using the code ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.Copy — Kris B, Nov 12 '15 at 22:52

score 1 · Answer 1 · answered Nov 12 '15 at 23:23

1

You could set an object variable of type TextFrame and then set that to your shape's TextFrame in order to pass it between various apps.

e.g.

Dim myFormattedText as TextFrame ' or TextFrame2 to access all of the newer properties
Set myFormattedText = ActivePresentation.Slides(1).Shapes(1).TextFrame ' or TextFrame2

That way you have all of the text properties within that object.

answered Nov 12 '15 at 23:23

Jamie Garroch - MVP

2,839
2
16
24

Hmm ok, but how do I pass it from file to file? Also is there a way to test if it's capturing the text correctly? – Joe K Nov 12 '15 at 23:31
To pass an object like this between files you could for example simply copy the object from a PowerPoint slide to an Excel sheet, just as you would using the UI. Or you could reference it in code within the VBE hosted by your chosen app (e.g. Excel or PowerPoint) and copy properties between apps. – Jamie Garroch - MVP Nov 16 '15 at 21:28
I know that I'll need to reference the object using code, but honestly I have no clue how to go about that and am not sure how to discern other bits on SO that are close to what I want. Could you possibly get me started, even with a link? Also, what kind of object would I use? Is the TextFrame an object itself that can be passed? – Joe K Nov 19 '15 at 01:53

score 1 · Answer 2 · answered Nov 20 '15 at 09:04

This should help get you going. It copies a shape object, which contains all of the formatting properties you want, from one open presentation to a second open presentation. In reality, you would make the presentation, slide and shape references dynamic based on what you're trying to achieve but this is a working example to demonstrate the principle:

Option Explicit

' Macro to copy the first shape from the first slide of open presentation 1 to the first slide of open presentation 2
' Requires that 2 presetnations are open and that the first has a shape on slide 1
' Wriiten by Jamie Garroch of youpresent.co.uk
Sub PassTextBoxBetweenPresentations()
  Dim oSrcShp As Shape ' Source Shape in Presentation 1
  Dim oTgtShp As Shape ' Source Shape in Presentation 2

  ' Set a reference to a shape in the first presentation, in this case, the first shape on the first slide
  Set oSrcShp = Presentations(1).Slides(1).Shapes(1)

  ' Copy the shape (with all of its properties) to the clipboard
  oSrcShp.Copy

  ' Paste the shape from the first presentation to the second presentation
  Presentations(2).Slides(1).Shapes.Paste

  ' Set a reference to the pasted shape
  Set oTgtShp = Presentations(2).Slides(1).Shapes(Presentations(2).Slides(1).Shapes.Count)

  ' Do stuff with your pasted shape object
  With oTgtShp
    Dim headlines
    If .HasTextFrame Then
      If .TextFrame.HasText Then
        headlines = .TextFrame.TextRange.Text
        Debug.Print headlines
      End If
    End If
  End With

  ' Clean up
  Set oSrcShp = Nothing: Set oTgtShp = Nothing
End Sub

Thanks, Jamie this definitely helps me understand what you were talking about! I guess my main concern at this point is whether I can even transfer text with formatting from PPT to Outlook? — Joe K, Feb 18 '16 at 14:16

Pass text with formatting as variable from file to file using vba

2 Answers2