• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Font size modification (excel range to powerpoint)

Villalobos

Active Member
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!

Code:
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
 
I tried in various way but I cannot change the font size of myShapeRange object.
Please, see the attached file.
 

Attachments

  • sample.xlsm
    21.5 KB · Views: 2
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..
 

Attachments

  • sample (1).xlsm
    21.7 KB · Views: 4
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
... 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.
 
Back
Top