Run-time error 1004, Copy paste chart from excel to powerpoint

[ktan075]

Hi all,

I wrote a sub that gets called when I need to copy and paste a chart from Excel 2010 to Powerpoint 2010. The sub gets inputs of (SlideNum, XLSSheetName, ChartName, XPos, YPos) to determine which gets copied and which slide it gets copied to.

However, I keep getting run time error 1004 (application-defined or object-defined error) on this line. "ActiveSheet.ChartObjects(ChartName).Copy"

Any help would be most appreciated!

Thanks so much!



Sub XLStoPPT(SlideNum, XLSSheetName, ChartName, XPos, YPos)

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")

' Reference active presentation
Set PPPres = PPApp.ActivePresentation

' Reference specific slide
Set PPSlide = PPPres.Slides(SlideNum)
Sheets(XLSSheetName).Activate
' Copy chart
ActiveSheet.ChartObjects(ChartName).Copy

'Go to required slide
PPApp.ActiveWindow.View.GotoSlide Index:=SlideNum
PPPres.Application.Activate
PPSlide.Shapes.PasteSpecial(DataType:=ppPastePNG).Select

' Position and size pasted chart
PPApp.ActiveWindow.Selection.ShapeRange.Left = XPos
PPApp.ActiveWindow.Selection.ShapeRange.Top = YPos

End Sub

 

[AlphaFrog]

The error is indicating it cannot find the chart object. Either the chart name is incorrect or the chart that is named is not located on the sheet that is named. All of that is determined outside this procedure.

This code does the same as your current code but cleaned up a bit. It probably doesn't fix the current issue though.

[color=darkblue]Sub[/color] XLStoPPT(SlideNum, XLSSheetName, ChartName, XPos, YPos)
    
    [color=green]' Copy chart[/color]
    Sheets(XLSSheetName).ChartObjects(ChartName).Copy
    
    [color=green]' Paste Chart[/color]
    [color=darkblue]With[/color] GetObject(, "Powerpoint.Application").ActivePresentation.Slides(SlideNum)
        [color=darkblue]With[/color] .Shapes.PasteSpecial(DataType:=ppPastePN)
            [color=green]' Position and size pasted chart[/color]
            .Left = XPos
            .Top = YPos
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    MsgBox "Chart pasteed to PowerPoint", , "Chart Paste Complete"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[ktan075]

Thanks AlphaFrog! You are right, it doesn't fix my problem. But i like your code better as it much cleaner.

The sub I wrote and the sub you wrote works sometimes and sometimes it doesn't. I cannot figure out a pattern for when it works and when it doesn't. I call the sub 4 times each time I run my marco. Sometimes it..

• does not work at the first call (copies 0 images)
• sometimes it stops working at the third (copies 3 images and fails on 4th)
• occasionally it works all 4 times (Copies all 4 images).

But.. it always works when I use this copy and paste code (Option A).. (except that this does not give me the format I want)

Option A:

ActiveSheet.ChartObjects(ChartName).chart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, format:=xlPicture
PPSlide.Shapes.Paste.Select

instead of...
Option B:

ActiveSheet.ChartObjects(ChartName).Copy
PPSlide.Shapes.PasteSpecial(DataType:=ppPastePNG).Select

However, if i run Option A first, then swap my code to Option B, Option B works fine!
Then when i close and re-open the excel file, Option B no longer fully works or does not work at all.......

Any ideas why that is?

 

[AlphaFrog]

Sorry no ideas.