Solved

Pasting a de-linked COPY of Charts from Excel into Powerpoint

Posted on 2009-05-07
2
1,190 Views
Last Modified: 2013-11-10
I have writted the Subroutine below which walks through a list and copies slides based on a Spreadsheet into a Powerpoint Presentation.  The problem is this:
 
Even though I've selected msoFalse for Linked in the PastSpecial command, the chart images are still linked and when the spreadsheet charts change, the pasted images in the Powerpoint Presentation also change, so I end up with 17 slides all with the same 4 images on them.

How do I break the linkage when I paste the image so that it actually pastes the image and delinks it immediately from the source image (so that when I change the source image, the pasted image doesn't change)
Sub MakePicQuads(ByVal ControlID As IRibbonControl)

    'This macro will copy all Charts to a PowerPoint Presentation

    'Each chart will become part of a 'quad' slide

    

    Dim myPPT As New PowerPoint.Application

    Dim myPres As PowerPoint.Presentation

    Dim PPSlide As PowerPoint.Slide

    Dim thisChart As Chart

    Dim thisShape As Shape

    Dim thisSheet As Worksheet

    Dim quadCount As Integer

        

    myPPT.Visible = True

    

    Set myPres = myPPT.Presentations.Add(msoTrue)

    myPPT.ActiveWindow.ViewType = ppViewSlide

    quadCount = 0

                  

    For Each thisSheet In Application.ActiveWorkbook.Worksheets

    For Each thisShape In thisSheet.Shapes

        ' copy chart

        If thisShape.Type = msoChart Then

            Set thisChart = thisShape.Chart

            thisChart.CopyPicture

          

            If quadCount = 0 Then

        

            ' Add a new slide

                SlideCount = myPres.Slides.Count

                Set PPSlide = myPres.Slides.Add(SlideCount + 1, ppLayoutBlank)

                myPPT.ActiveWindow.View.GotoSlide PPSlide.SlideIndex

            End If

        

            ' paste and select the chart

            'PPSlide.Shapes.PasteSpecial(ppPasteOLEObject, , , , , msoFalse).Select

            PPSlide.Shapes.PasteSpecial ppPasteEnhancedMetafile, , , , , msoFalse

                            

            'Set the size and location depending on which slide it is.

            myPPT.ActiveWindow.Selection.ShapeRange.Height = 180

            myPPT.ActiveWindow.Selection.ShapeRange.Width = 325

            myPPT.ActiveWindow.Selection.ShapeRange.Top = IIf(quadCount > 1, 300, 100)

            myPPT.ActiveWindow.Selection.ShapeRange.Left = IIf(quadCount Mod 2 = 0, 30, 370)

            

            quadCount = quadCount + 1

            If quadCount > 3 Then

                quadCount = 0

            End If

        End If

    Next

    Next
 

End Sub

Open in new window

0
Comment
Question by:rgautier
  • 2
2 Comments
 
LVL 13

Author Comment

by:rgautier
ID: 24324774
Edit: I pasted the wrong code above...here's the one I'm working on
Sub TheList()

    'This macro will copy all Charts to a PowerPoint Presentation

    'Each chart will become part of a 'quad' slide

    

    Dim myPPT As New PowerPoint.Application

    Dim myPres As PowerPoint.Presentation

    Dim PPSlide As PowerPoint.Slide

    Dim thisChart As Chart

    Dim thisShape As Shape

    Dim thisSheet As Worksheet

    Dim quadCount As Integer

        

    myPPT.Visible = True

    

    Set myPres = myPPT.Presentations.Add(msoTrue)

    myPPT.ActiveWindow.ViewType = ppViewSlide

    quadCount = 0

    Dim myRow As ListRow

    For Each myRow In ActiveSheet.ListObjects("TheListofMDs").ListRows

        ActiveSheet.PivotTables("PivotTable1").PivotFields("design").ClearAllFilters

        ActiveSheet.PivotTables("PivotTable1").PivotFields("design").CurrentPage = myRow.Range(, 1).Value

    For Each thisSheet In Application.ActiveWorkbook.Worksheets

    For Each thisShape In thisSheet.Shapes

        ' copy chart

        If thisShape.Type = msoChart Then

            Set thisChart = thisShape.Chart

            thisChart.ChartArea.Copy

          

            If quadCount = 0 Then

        

            ' Add a new slide

                SlideCount = myPres.Slides.Count

                Set PPSlide = myPres.Slides.Add(SlideCount + 1, ppLayoutBlank)

                myPPT.ActiveWindow.View.GotoSlide PPSlide.SlideIndex

            End If

        

            ' paste and select the chart

            'PPSlide.Shapes.PasteSpecial(ppPasteOLEObject, , , , , msoFalse).Select

            PPSlide.Shapes.Paste.Select

                            

            'Set the size and location depending on which slide it is.

            myPPT.ActiveWindow.Selection.ShapeRange.Height = 180

            myPPT.ActiveWindow.Selection.ShapeRange.Width = 325

            myPPT.ActiveWindow.Selection.ShapeRange.Top = IIf(quadCount > 1, 300, 100)

            myPPT.ActiveWindow.Selection.ShapeRange.Left = IIf(quadCount Mod 2 = 0, 30, 370)

            

            quadCount = quadCount + 1

            If quadCount > 3 Then

                quadCount = 0

            End If

        End If

    Next

    Next

    Next
 

End Sub

Open in new window

0
 
LVL 13

Accepted Solution

by:
rgautier earned 0 total points
ID: 24324810
And....that explains everything - I have been editing the wrong function - never mind.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Many programs have tried to outwit PowerPoint in terms of technology and skill. These programs, however, still lack several characteristics that PowerPoint has possessed from the start. Here's why PowerPoint replacements won't entirely work for desi…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now