Red face Excel charts as jpgs to specific powerpoint

sandra1990

New Member
Joined
Jan 22, 2018
Messages
3
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? I can provide you with the excel file.


Thank you in advance.
 

sandra1990

New Member
Joined
Jan 22, 2018
Messages
3
Hi guys,

I went further, please find the code below.
HTML:
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?
 

sandra1990

New Member
Joined
Jan 22, 2018
Messages
3
The above problems are solved (please see the code below).

I am running into problems if there are not enough slides in the presentation.
Could only someone please tell me how to add the slides I have the code for it but dont know how and where to implement it, so that after pasting the three shapes another slide is going to be added and on this slide the subsequent shapes are going to be pasted.

Code:
ActivePresentation.Slides.Add Index:=ActivePresentation.Slides.Count + 1, Layout:=ppLayoutCustom

Code:
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 3 = 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 = 30 + ((i Mod 3) * 200)
        myShape.Top = 66
        i = i + 1


        
    Next cht
    
            
        For Each cht2 In Worksheets("Beautiful").ChartObjects
        'Add a slide to the Presentation
        If i Mod 3 = 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 = 30 + ((i Mod 3) * 200)
        myShape.Top = 66
        i = i + 1
        
    Next cht2


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


End Sub
 

Forum statistics

Threads
1,081,560
Messages
5,359,608
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top