Maintain Font Formatting from Excel into Powerpoint

Question

I have a VBA macro that pastes charts from Excel into PowerPoint and this works.

I've adjusted the macro to copy the cell contents of certain cells of the same worksheet into PowerPoint. Despite setting the font size to be the same in Excel as it is in PowerPoint template before the macro copies it (40 point in both) the font gets pasted in at 18 point rather than 40 which I have set in Excel prior to the Macro running.

The code that handles all of this is as follows:

'Opens a new PowerPoint presentation based on template and pastes data into Slide 2 of Powerpoint from Excel

Dim PPApp As PowerPoint.Application, PPPres As PowerPoint.Presentation, PPSlide As PowerPoint.Slide, PPShape As Object, PPChart As Object

Dim XLws As Worksheet

Set XLws = ActiveSheet
Set PPApp = New PowerPoint.Application
Set PPPres = PPApp.Presentations.Open("C:\Users\Colin\Dropbox (Edge45)\Edge45 Team Folder\Edge45 Company Documents\Templates\Powerpoint Templates\Edge45 Monthly Report Template Macro.potm", Untitled:=msoTrue)
PPApp.Visible = True
Set PPSlide = PPPres.Slides(12)

XLws.ChartObjects(1).Copy ' or XLws.ChartObjects("Chart 2").Copy
Set PPChart = PPSlide.Shapes.PasteSpecial(DataType:=ppPasteDefault)

With PPChart

.Left = 18
.Height = 322
.Top = 121
.Width = 531

End With

'Copies Contents of Cells Q36 and R36 in excel worksheet and pastes as text into slide 13 of presentation
  XLws.Range("Q36:R36").Copy
  Set PPChart = PPSlide.Shapes.PasteSpecial(DataType:=ppPasteDefault)

Set PPSlide = PPPres.Slides(13)

Yes, I'd be happy to do that but firstly, it includes a line divide between two cells despite specifying no internal border and secondly, I'd like to have a drop shadow on the text I am copying - If I can get that from an image paste, happy to do so! — Superhans
Don't you need to set PPSlide to slide 13 before pasting the data from XLws.Range("Q36:R36")? — mooseman

Profex Profex · Accepted Answer · 2017-04-05T15:33:01

Colin,

Are you changing the font size back after you copy it? Maybe it's linked/updating automatically.

If you can copy it in manually using one of the paste options I'd suggest using the ExecuteMso command referenced in the following post to mimic more accurately what you are doing:

How to copy a chart from Excel to PowerPoint?

Maintain Font Formatting from Excel into Powerpoint

1 Answers