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
– TomDillinger, Commented Jul 27, 2015 at 3:47
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
– Samuel Huang, Commented Jul 27, 2015 at 3:53
As in the text size causes it to extend beyond the page, or its only copying half the characters? — TomDillinger
– TomDillinger, Commented Jul 27, 2015 at 3:55
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
– Samuel Huang, Commented Jul 27, 2015 at 7:05

Community · Accepted Answer · 2017-05-23 12:06:08Z

0

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

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Jul 27, 2015 at 4:00

TomDillinger

1947 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adarsha · Accepted Answer · 2015-07-27 07:15:06Z

0

My hunch is you need to add Paste:=xlPasteAllUsingSourceTheme to paste special. Can you try this?

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

answered Jul 27, 2015 at 7:15

Adarsha

2,37723 silver badges29 bronze badges

3 Comments

Samuel Huang Over a year ago

Are you suggesting that i replace this line mySlide.Shapes.PasteSpecial DataType:=ppPasteDefault with this line? mySlide.Shapes.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, DataType:=ppPasteDefault

Adarsha Over a year ago

Yes, note that only change is addition of xlPasteAllUsingSourceTheme

Samuel Huang Over a year ago

Error, named argument not found. I think it's because I'm trying to copy it over to PPT which renders it unworkable.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related