Link to home
Start Free TrialLog in
Avatar of fb1990
fb1990

asked on

VBA to Copy Text from Excel to PowerPoint

Hello EE,

Can someone please help demonstrate how to copy Text from Excel to a PowerPoint shape.  I have a vba that creates PowerPoint slides and copy charts to PowerPoint.  I also have Text in excel that i want to copy and be placed on the slide and inside a shape on the slide

Please help
Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)
Flag of United Kingdom of Great Britain and Northern Ireland image

It all depends on how you plan to identify the PowerPoint application, presentation, slide and shape objects but since you're already doing this, the following should point you in the right direction:

Option Explicit

Sub CopyTextFromCellToPowerPointShape()
  ' PowerPoint objects
  Dim oPPT As Object
  Dim oPres As Object
  Dim oSld As Object
  Dim oShp As Object
  
  ' PowerPoint constants
  Const ppPasteRTF = 9
  Const ppPasteText = 7
  
  ' Excel objects
  Dim oWB As Workbook
  Dim oWS As Worksheet
  
  Set oWS = ActiveSheet
  oWS.Range("A1").Copy
  
  Set oPPT = GetObject(, "PowerPoint.Application")
  Set oPres = oPPT.ActivePresentation
  Set oSld = oPres.Slides(1)
  Set oShp = oSld.Shapes(1)
  
  ' This sets the unformatted text
  oShp.TextFrame.TextRange.Text = oWS.Range("A1").Text
  
  ' This pastes the text without format
  oShp.TextFrame.TextRange.PasteSpecial ppPasteText
  
  ' This pastes the text with the source format
  oShp.TextFrame.TextRange.PasteSpecial ppPasteRTF
End Sub

Open in new window


You'll need to add cases for objects not existing etc. and error handling and object clean up.
Avatar of fb1990
fb1990

ASKER

Hi Jamie,

Here is a code from my vba to specify the text, but i want to reference  cell C10 in Sheet3 of my excel and paste to the focus slide

   With PPSlide.Shapes.AddShape(Type:=msoShapeRectangle, _
      Left:=550, Top:=175, Width:=350, Height:=240).TextFrame _
      .TextRange.Text = "Some Text in cell  C120 of Sheet3"
    End With

Open in new window

Is it possible specify the cell # and Sheet where the comments should come from?

Thnaks for your help
SOLUTION
Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fb1990

ASKER

Thanks, Jamie for your assistance