Using VBA to paste Excel data into Powerpoint, Font Size not changing

Question

new to stack overflow posting, so do guide me along. :)

I am required to create a program to paste certain values from excel to powerpoint and format it.

The VBA code I have written so far is able to produce the Cell Value from Excel to PowerPoint. However, the Font Size does not change in Powerpoint while the Font Colour and Font Type will change. I am unsure why this is so but I have a thought that it could be due to some powerpoint settings or something regarding VBA I am not familiar with.

Please Assist!

Codes are below:

Dim title1 As Excel.range
Dim App As PowerPoint.Application
Dim Ptn As PowerPoint.Presentation
Dim mySlide As PowerPoint.Slide
Dim myShape As PowerPoint.Shape

Set title1 = ThisWorkbook.Sheets("Sheet4").range("C1:C1")

'Set Font Properties
title1.Font.Size = 28
title1.Font.Color = RGB(0, 112, 192)
title1.Font.Name = "Century Gothic"


'Create Slide 1
Set mySlide = Ptn.Slides.Add(1, ppLayoutBlank)

'Copy Title1
title1.Copy

'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=ppPasteDefault

Set myShape = mySlide.Shapes(mySlide.Shapes.Count)

'Set position
myShape.Left = 35
myShape.Top = 200

Try replacing: mySlide.Shapes.PasteSpecial DataType:=ppPasteDefault with mySlide.Shapes.PasteSpecial ppPasteOLEObject — TomDillinger
It cuts the text off halfway through when I did that. Any way to resize the object so that the whole line of text is displayed instead? — Samuel Huang
As in the text size causes it to extend beyond the page, or its only copying half the characters? — TomDillinger
When it is pasted as an OLEObject what is shown is only the first 3 letters of the text, as an image(?) or some sort of object. If I try to lengthen it by dragging the right border horizontally, it only expands the cut off text. — Samuel Huang

TomDillinger TomDillinger · Accepted Answer · 2015-07-27T04:00:42

If you want to specifically paste your excel field as the title of that slide try:

mySlide.Shapes.AddTitle.TextFrame.TextRange.Text = title1

Edit: you can check if your slide already has a title you can use

If Not mySlide.Shapes.HasTitle Then
    mySlide.Shapes.AddTitle.TextFrame.TextRange.Text = title1
Else
   mySlide.Shapes.Title.TextFrame.TextRange.Text = title1
End If

AddTitle also has various properties for setting font, format, size, color etc. See: Adding a Powerpoint Title slide using Excel VBA

Using VBA to paste Excel data into Powerpoint, Font Size not changing

2 Answers