none
Copy a graph in Excel and Paste in PowerPoint (VBA) --> gives error RRS feed

  • Question

  • Hi,

    I have a graph in Excel from C1 to AC12. I want to copy that graph into a PowerPoint and I have to be able to change some of the cells when it's in the PowerPoint slide. So the graph has to get exactly copied.

    The complete code:

    Sub Test()
    
    Dim ApplPP As Object
    Dim PrsntPP As Object
    Dim SlidePP As Object
    Dim ShapePP As Object
        
        'Create a new instance of the PowerPoint application, if an existing PowerPoint object is not available.
        'Set the Application object as follows:
        On Error Resume Next
        
        Set ApplPP = GetObject(, "PowerPoint.Application")
        
        'If an instance of an existing PowerPoint object is not available, an error will occur (Err.Number = 0 means no error):
        If Err.Number <> 0 Then
            Set ApplPP = CreateObject("PowerPoint.Application")
        End If
        
        'Disable error handling:
        On Error GoTo 0
        
        'PowerPoint window visible:
        ApplPP.Visible = True
        
        'Create a new presentation in PowerPoint:
        Set PrsntPP = ApplPP.Presentations.Add
        
    '=============================================================================================================================
        'Add first slide
        Set SlidePP = PrsntPP.Slides.Add(Index:=1, Layout:=11)
        
        'Title (Shape 1) --> Delete
        SlidePP.Shapes.Title.Delete
                
       'Table
        ThisWorkbook.Worksheets("Sheet1").Range("C1:AC12").Copy
        SlidePP.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=True
        
        Set ShapePP = SlidePP.Shapes(SlidePP.Shapes.Count)
        
        With ShapePP
        
            .Top = 60
            .Left = 0
            .Width = 800
            .Height = 196
        
        End With
    
    End Sub
    


    I have tried 2 things:

    1) 

    'Table
    
        ThisWorkbook.Worksheets("Sheet1").Range("C1:AC12").Copy
        SlidePP.Shapes.Paste

    When I use this, an error occurs: Shapes.Paste: Invalid request. Clipboard is empty or contains data which may not be pasted here.

    2) 

    'Table
    
        ThisWorkbook.Worksheets("Sheet1").Range("C1:AC12").Copy
        SlidePP.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=True

    When I use this, the graph is copied and pasted, but I can not change it anymore in PowerPoint.

    Is there anything to solve these problems?

    Thanks!

    Thursday, October 20, 2016 8:16 AM

All replies

  • Hi GaneshBhard,

    I try to test your code and find that when you use below mentioned code The Range data are copied to the ppt file as an picture. so you cannot edit that from powerpoint but it is linked with Excel file.

    so if you change the data in the Excel file then data will be automatically changed in powerpoint file.

    'Table
    
        ThisWorkbook.Worksheets("Sheet1").Range("C1:AC12").Copy
        SlidePP.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=True


    if we talk about the line of code mentioned below then it also run and paste the Range data on ppt file successfully on my side. I did not get any error. the data are also editable. but it is not support the "Link" property. so whether you change data in ppt file or you changed data in excel. it will not be reflected in other file.

    'Table
    
        ThisWorkbook.Worksheets("Sheet1").Range("C1:AC12").Copy
        SlidePP.Shapes.Paste

    so in your original post you had mentioned that you got an error but when I run this code it runs without any error.

    so I suggest you to recheck your code and test it again. if you have same issue then let me know.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 21, 2016 4:33 AM
    Moderator
  • The problem is still there..

    Friday, October 21, 2016 7:31 AM
  • Hi Ganesh,

    can you show me your graph data?

    also I want to know your goal.

    I assume that you want do like if you update the data in powerpoint file then it should be automatically updated in Excel? correct me if I am wrong.

    if you solve this error then also this will not happen with above mentioned code.

    with paste.special you are only able to change the data from excel and in powerpoint it will get changed by itself but not vice versa.

    using only paste you are able to update the data in powerpoint file but it will not get changed in excel file and also if you change in excel file then also it will not be changed in powerpoint file.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 21, 2016 9:13 AM
    Moderator
  • I can not show you the graph data, because it's data I may not share.

    I don't want the thing you're suggesting. I just can't figure out why the code is not working. It needs to copy and paste some graphs into PowerPoint, but Excel won't do that somehow. I want to know why that is.

    Friday, October 21, 2016 9:37 AM
  • Hi Ganesh,

    As I told you your code is working on my side. both the code segment you had mentioned copying the data without any error.

    so for that I want to know about your graph data to know whether it is something different then I am trying to test.

    so that I can do accurate testing and can give you a suggestion.

    I don't need your confidential data. you can provide a dummy data that can make a effect and can able to reproduce the issue on my side.

    you had mentioned that "you don't want the thing I suggested you". let me inform you that I just informed you how your code will work that's it. there is no any suggestion because I did not reproduce the issue mentioned by you in the original post with the code posted by you.

    below is the result I got when I run your code on my side with my dummy data in different range.

    using Paste.Special

    using only paste.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 25, 2016 12:03 AM
    Moderator
  • Hi Ganesh

    In case you are still reading this thread: I faced the same (or at least a very similar issue) when copying elements from one PPT presentation into another. For no obvious reason sometimes the "clipboard is empty" message appeared. After having added "DoEvents" between the lines calling the copy and the paste method everything worked fine.

    Maybe that helps in your case also.

    Cheers, Luca

    Monday, November 14, 2016 3:50 PM