Unlinking an Excel chart copied and pasted into PowerPoint

The code below does a nice job pasting the copied chart into PowerPoint, but I need something in line 20 that will unlink the chart. I don't want to paste it as a picture because I want to have control over the data.

Sub ChartToPresentation()
Sheets(i).ChartObjects("Chart 1").Copy
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
    Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation ' Reference active presentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
   ' PPPres.Slides(i + 4).Select
    With PPPres.Slides(i + 4).Shapes.Paste
        If i = 6 Then
        .top = 60
        ElseIf i = 7 Or i = 9 Then
        .top = 120
        Else
        .top = 80
        End If
        .Left = 46
    End With
HERE I NEED A LINE OR TWO THAT WILL BREAK THE LINK
End Sub

Open in new window

Thanks!
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

pls try
    With PPPres.Slides(i + 4).Shapes.Paste
        If i = 6 Then
        .top = 60
        ElseIf i = 7 Or i = 9 Then
        .top = 120
        Else
        .top = 80
        End If
        .Left = 46
        .LinkFormat.BreakLink
    End With

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Rgonzo, it works great but the snippet of code above is from one of two macros (called by a third macro) that are run in sequence: "ChartToPresentation" and "ChartToPresentation2." Each of them needs your line of code. It runs perfectly until i = 5 when It bugs on the line, "With PPPres.Slides(i + 4).Shapes.Paste"  in ChartToPresentation2, giving this message: "Shapes (unknown member): Invalid request.Clipboard is empty or contains data that may not be pasted here."  However if I resume the macro by entering F5, it runs to completion! ???

How do I fix that?  Is there any way we can put the code at the end of the master macro ("PasteAllCstmrCharts"? It's not essential to select PPPres.Slides(7), if that helps.

Thanks,
John

Sub PasteAllCstmrCharts()
Call OpenPPT
For i = 3 To 9
'Sheets(i).Activate
Call ChartToPresentation
ThisWorkbook.Activate
here:
Next i
PPPres.Slides(7).Select
End Sub

Sub ChartToPresentation()
Sheets(i).ChartObjects("Chart 1").Copy
    Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    PPPres.Slides(i + 4).Select
    With PPPres.Slides(i + 4).Shapes.Paste
        If i = 6 Then
        .top = 60
        ElseIf i = 7 Or i = 9 Then
        .top = 120
        Else
        .top = 80
        End If
        .Left = 46
        .LinkFormat.BreakLink  'BUGS WHEN i = 
    End With
ThisWorkbook.Activate
Call ChartToPresentation2
End Sub

Sub ChartToPresentation2()
If i = 7 Or i = 9 Then Exit Sub
Sheets(i).ChartObjects("Chart 2").Copy
    Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    With PPPres.Slides(i + 4).Shapes.Paste
        .Left = 46
        If i = 4 Then
          .top = 305
        ElseIf i = 6 Then
          .top = 242
        Else
          .top = 270
        End If
        .LinkFormat.BreakLink
    End With
End Sub

Open in new window

Rgonzo1971Commented:
Let's try another method
Sub ChartToPresentation()
Set cht = Sheets(i).ChartObjects("Chart 1").Chart
cht.CopyPicture
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
    Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation ' Reference active presentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
   ' PPPres.Slides(i + 4).Select
    With PPPres.Slides(i + 4).Shapes.PasteSpecial(DataType:=ppPasteDefault, Link:=msoFalse)
        If i = 6 Then
        .Top = 60
        ElseIf i = 7 Or i = 9 Then
        .Top = 120
        Else
        .Top = 80
        End If
        .Left = 46
    End With

End Sub

Open in new window

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Rgonzo, sorry for going AWOL. I tried this but it also failed to unlink the PowerPoint. I'll probably just have to unlink it manually, but is there perhaps a standalone macro (written in Excel) that will unlink the active PPT Presentation globally? Or is there a macro I can include in my PowerPoint that the Excel macro can call at the end?

Thanks,
John
Rgonzo1971Commented:
then try ( it pastes a picture)

Sub ChartToPresentation()
Set cht = Sheets(1).ChartObjects("Chart 1").Chart
cht.CopyPicture
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
    Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation ' Reference active presentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
   ' PPPres.Slides(i + 4).Select
    With PPPres.Slides(1).Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture, Link:=msoFalse)
        If i = 6 Then
        .Top = 60
        ElseIf i = 7 Or i = 9 Then
        .Top = 120
        Else
        .Top = 80
        End If
        .Left = 46
    End With

End Sub

Open in new window

John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Rgonzo.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.