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?
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
0

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

0
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

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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
0
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

0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Rgonzo.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.