VBA - Keep source formatting when pasting charts from Excel to PowerPoint

2
votes

I want to copy-paste some charts located in an Excel workbook into PowerPoint slides using VBA. This part is easy, but I would also like to keep source formatting (= Excel formatting).

1) I first used this kind of basic code :

Worksheets("DailyActivityGeneral").ChartObjects("Chart 3").Copy
Set chartGen3 = prsntPP.Slides(16).Shapes.Paste

It worked fine except for the formatting, which isn't kept.

2) I then tried to use another method:

Worksheets("DailyActivityGeneral").ChartObjects("Chart 3").Copy
prsntPP.Slides(16).Select
prsntPP.Application.CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting")
prsntPP.Application.CommandBars.ReleaseFocus

This keeps the source formatting, which is great, but I cannot seem to be able to write:

set chartGen3 = prsntPP.Application.CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting")

(I get an error message) The problem is that I don't know how to manipulate the pasted chart (which is possible with the "chartGen3" like in the first method). Any idea about how to deal with the pasted chart ? This would probably solve my problem !

3) I noticed that when I use the second method once at the beginning of my code, (almost) all copy-paste actions now keep source formatting, even if they are written with the first syntax. So the best solution I've found, to be both able to handle my pasted chart and keep its source formatting, is to first use the second method, delete the slide because the chart cannot be manipulated, and then use the first method with all other slides.

But I have a last problem: It keeps source formatting as expected, but for unknown reasons, sometimes, it doesn't (random behaviour !)

How would you proceed to keep source formatting when copying charts from Excel to PowerPoint ?

Thanks !

1
excelvbachartsformattingpowerpoint

1 Answers

0
votes

It isn't a perfect solution, I don't think it's very clean, but at least it works :

In order to keep source formatting for ALL my charts, I have to regularly use the second method mentioned in my above post, and not once at the beginning of my code as I did first. It seems that its effects only remain valid for a limited amount of time (don't know why) - which explains the "random behaviour" I described.

I would have liked to find a better method but so far that's all I have !