Font size modification (excel range to powerpoint)

[Villalobos]

Hello,

I would like to ask some advice regarding font size modification (in Excel 2013 or above), because I do not find the correct way and I am lost.

The below mentioned code copy the Excel range then paste it in powerpoint but unfortunately I cannot change the font size (6 to 8) of myShapeRange object.

Do you have any idea how should it be done?
Thank you in advance any advice!

Option Explicit
Sub CreatePPtPresentation()
Dim ppApp As Object
Dim ppPres As Object
Dim ppSlide As Object
Dim SlideCount As Integer
Dim ppPasteEnhancedMetafile As Variant
Dim myShapeRange As Object
Dim OWidth As Variant
Dim OHeight As Variant

  Set ppApp = CreateObject("Powerpoint.Application")
  ppApp.Visible = True
  Set ppPres = ppApp.Presentations.Add
  SlideCount = ppPres.Slides.Count
  Set ppSlide = ppPres.Slides.Add(SlideCount + 1, 11)
  ppSlide.Shapes(1).TextFrame.TextRange.Text = "Business Gap Deployment " & Sheets(4).Range("B2").Value

  With ppSlide.Shapes(1).TextFrame.TextRange.Characters
  .Font.Size = 30
  .Font.Name = "Arial"
  .Font.Color = vbWhite
  End With
  
  With ppSlide.Shapes(1)
  .Fill.BackColor.RGB = RGB(79, 129, 189)
  .Height = 50
  End With
  
  Sheets(4).Range("B14:Y28").Copy
  Set myShapeRange = ppSlide.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile)
  
  With myShapeRange
  .Width = 1000
  OWidth = .Width
  OHeight = .Height
  .Width = 12.5 * 72
  .Height = OHeight * (.Width / OWidth)
  .Left = 70
  .Top = 100
  .LockAspectRatio = 0
  .Height = 400
  .Width = 820
  End With
  
  Set ppSlide = Nothing
  Set ppPres = Nothing
  Set ppApp = Nothing
  
End Sub

 

[vletm]

Villalobos
Have You tried to do it as below (did You miss number there?)

.shapes(1).TextFrame2.TextRange.Font.Size = 8

 

[Villalobos]

I have Run-time error 438 at this line.
Object doesn't support this property or method.

 

[vletm]

Villalobos
How did You modify Your code?
How do it look now?

 

[Villalobos]

I tried in various way but I cannot change the font size of myShapeRange object.
Please, see the attached file.

 

[vletm]

Villalobos
Your tried ... but not as I thought
I tried to change as I wrote,
but there were something ActiveX ...
and that's why I cannot test it.
You would test..

 

[Villalobos]

No change.
The font size of the Table on ppt slide is the same than before.

 

[vletm]

Villalobos
hmm ... so, I cannot help because I cannot test it.
Good luck.

 

[Villalobos]

Anyhow, thank you for your time and effort!
Does anybody have any idea?

 

[vletm]

Villalobos
It tried to check few more things ... and ...
Are You sure that it's possible to change font size AFTER paste?
... I cannot paste as 'EnhancedMetafile' ... You can
... Have You tried to change it's font size manually from PowerPoint?

 

[Villalobos]

Yes, I can change it manually from PowerPoint (but I would like to avoid the manual steps if possible).

 

[vletm]

Villalobos
... then start recording a new macro from Excel
and do all steps as You would like to do
... stop recording.
then You would get an idea of that code.