Excel charts as jpgs to specific powerpoint

[sandra.pa]

Hi guys,


I am new and please forgive me, if am not specific enough.


I would like to export all of the charts using vba code in a horizontal order from a specific worksheet (sheet1) to a specific powerpoint which is already saved at the end of a given path.
The powerpoint file as well as the path where it is located are given in cells (please find the attached excel with charts, Cells W7 and W8).


Could you please help me with the code?


Thank you in advance.

21431:cloud9::blush:cloud9:
21430

[JonPeltier]

You really want to copy the charts and paste them into the PowerPoint presentation. This requires VBA that

- Accesses PowerPoint
- Opens the PowerPoint file
- Copies an Excel chart
- Pastes the chart in a given format onto a slide (and use PNG, not JPG)

If you search this forum for 'paste excel chart into powerpoint', you should find some pages that will get you started.

[sandra.pa]

Hi guys,

I got further, please find the code below.



Sub ExcelToPowerPoint()


Dim cht As ChartObject
Dim cht2 As ChartObject
Dim rng As Excel.Range
Dim PowerPointApp As PowerPoint.Application
Dim myPresentation As PowerPoint.Presentation
Dim mySlide As PowerPoint.Slide
Dim myShapeRange As PowerPoint.Shape
Dim i As Long, j As Long

'Create an Instance of PowerPoint
On Error Resume Next
'Is PowerPoint already opened?
Set PowerPointApp = GetObject(class:="PowerPoint.Application")

'Clear the error between errors
err.Clear

'If PowerPoint is not already open then open PowerPoint
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")

'Handle if the PowerPoint Application is not found
If err.Number = 429 Then
MsgBox "PowerPoint could not be found, aborting."
Exit Sub
End If

On Error GoTo 0

'Open presentation listed in W7 and W8
On Error GoTo err
Set myPresentation = PowerPointApp.Presentations.Open(Range("w8") & "" & Range("w7"))

'Make PowerPoint Visible and Active
PowerPointApp.Visible = True
PowerPointApp.Activate

i = 0 'counter for chart
j = 0 'counter for slide

For Each cht In Worksheets("Nice").ChartObjects
'Add a slide to the Presentation
If i Mod 2 = 0 Then
j = j + 1
End If

Set mySlide = myPresentation.Slides(j)

'Copy Excel Range
cht.Activate
ActiveChart.ChartArea.Copy

'Paste to PowerPoint
mySlide.Shapes.PasteSpecial DataType:=ppPasteJPG
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
With myShape
.LockAspectRatio = True
.Width = 200 'points wide
End With
'Set position:
myShape.Left = 40 + ((i Mod 2) * 250)
myShape.Top = 66
i = i + 1

Next cht

For Each cht2 In Worksheets("Beautiful").ChartObjects
'Add a slide to the Presentation
If i Mod 2 = 0 Then
j = j + 1
End If

Set mySlide = myPresentation.Slides(j)

'Copy Excel Range
cht2.Activate
ActiveChart.ChartArea.Copy

'Paste to PowerPoint
mySlide.Shapes.PasteSpecial DataType:=ppPasteJPG
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
With myShape
.LockAspectRatio = True
.Width = 200 'points wide
End With
'Set position:
myShape.Left = 40 + ((i Mod 2) * 250)
myShape.Top = 66
i = i + 1

Next cht2
Exit Sub

err:
MsgBox "File name does not exist, please check and try again"
End Sub


Three issues are bothering me however.
1. I get "out of memory error"
2. I get the msg box "File name does not exist, please check and try again" although the job has been done appropriately (powerpoint opened, pictures pasted).
3. In the sheet "Nice" I prepared three charts in one line. Unfortunately only two are being pasted.

Could you please comment on that?

[JonPeltier]

You have "On Error GoTo err", and err gives you the "File doesn't exist" error. If the file is opened, then the error occurs later in the code, but it still gives the message in err. Obviously something else caused the error.

Remove the On Error, and see where the code halts, and fix that problem. Since the third chart isn't pasted, I suspect there's a problem with the third time through the loop.