macro to copy chart from excel to powerpoint

Dear Rich,

Is there anyway I can have the sintex extract any data written on the right of the Chart (J column) without define a specific cell?

Right now I have anything in J7 to J8 and J27 to J29. (Is there anyway to broading that instead of having it pull from a specific cell)

'If the chart is the "US" consumption chart, then enter the appropriate comments

If InStr(activeSlide.Shapes(1).TextFrame.TextRange.Text, "US") Then

activeSlide.Shapes(2).TextFrame.TextRange.Text = Range("J7").Value & vbNewLine

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J8").Value & vbNewLine)

'Else if the chart is the "Renewable" consumption chart, then enter the appropriate comments

ElseIf InStr(activeSlide.Shapes(1).TextFrame.TextRange.Text, "Renewable") Then

activeSlide.Shapes(2).TextFrame.TextRange.Text = Range("J27").Value & vbNewLine

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J28").Value & vbNewLine)

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J29").Value & vbNewLine)

Hi,

I tidied up your code, take a look at this - there are some handy tips highlighted, namely:

• Always name your ppt variables such that it is obvious they are ppt variables and not Excel variables (pptSld, pptShp, etc.)
• Avoid confusion between objects of type "Chart" and "ChartObject" (so I used "chtObj" below)
• Always put what you are looping after the word Next  (Next chtObj)
• The title shape of a slide can be references using pptSld.Shapes.Title
• No need to select things (usually), hence I used: pptSld.Shapes.Paste    and then you can grab the newly pasted shape as it will always be the last (highest z-order) shape on the slide:

    Set pptShpCht = pptSld.Shapes(pptSld.Shapes.Count)

• Use a With - End With routine to shorten your code, and also make more use of objects to shorten your code.

Here's the code:

Sub CreatePowerPoint()

'Add a reference to the Microsoft PowerPoint Library by:'1. Go to Tools in the VBA menu'2. Click on Reference'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay   

    'First we declare the variables we will be using

    Dim chtObj As Excel.ChartObject

    Dim ppt As PowerPoint.Application

    Dim pptSld As PowerPoint.Slide

    Dim pptShpCht As PowerPoint.Shape, pptShpPH As PowerPoint.Shape

    'Look for existing instance of ppt:

    On Error Resume Next

    Set ppt = GetObject(, "PowerPoint.Application")

    On Error GoTo 0

    'Create a new PowerPoint instance if required:

    If ppt Is Nothing Then Set ppt = New PowerPoint.Application

    'Make a presentation in PowerPoint

    If ppt.Presentations.Count = 0 Then ppt.Presentations.Add

    'Show the PowerPoint

    ppt.Visible = True

    'Loop through each chart in the Excel worksheet and paste them into the PowerPoint

    For Each chtObj In ActiveSheet.ChartObjects

        'Add a new slide where we will paste the chart

        ppt.ActivePresentation.Slides.Add ppt.ActivePresentation.Slides.Count + 1, ppLayoutText

        ppt.ActiveWindow.View.GotoSlide ppt.ActivePresentation.Slides.Count

        Set pptSld = ppt.ActivePresentation.Slides(ppt.ActivePresentation.Slides.Count)

        'Copy the chart and paste it into the PowerPoint as a Metafile Picture

        chtObj.Chart.ChartArea.Copy

        pptSld.Shapes.Paste                  'WILL WORK OK IN 2010 BUT MAY NOT WORK IN 2013!

        Set pptShpCht = pptSld.Shapes(pptSld.Shapes.Count)

      'Set the title of the slide the same as the title of the chart

        pptSld.Shapes.Title.TextFrame.TextRange.Text = chtObj.Chart.ChartTitle.Text

        'Adjust the positioning of the Chart on Powerpoint Slide

        pptShpCht.Left = 15

        pptShpCht.Top = 125

        'Adjust positioning of the PlaceHolder shape

        Set pptShpPH = pptSld.Shapes(2)

        pptShpPH.Width = 200

        pptShpPH.Left = 505

        With pptShpPH.TextFrame.TextRange

            'If the chart is the "US" consumption chart, then enter the appropriate comments

            If InStr(pptSld.Shapes.Title.TextFrame.TextRange.Text, "US") Then

                .Text = Range("J7").Value & vbNewLine

                .InsertAfter (Range("J8").Value & vbNewLine)

          'Else if the chart is the "Renewable" consumption chart, then enter the appropriate comments

            ElseIf InStr(pptSld.Shapes.Title.TextFrame.TextRange.Text, "Renewable") Then

                .Text = Range("J27").Value & vbNewLine

                .InsertAfter (Range("J28").Value & vbNewLine)

                .InsertAfter (Range("J29").Value & vbNewLine)

            End If

            'Change the font size of the PlaceHolder:

            .Font.Size = 16

        End With

    Next chtObj

    'AppActivate "Microsoft PowerPoint"

    Set pptShpCht = Nothing

    Set pptShpPH = Nothing

    Set pptSld = Nothing

    Set ppt = Nothing

End Sub

As for your question about getting information from the cells, you can use either:

    chtObj.BottomRightCell

or

    chtObj.TopLeftCell

in conjuction with .Offset to grab cells relative to the position of the chart object.  For example:

     .Text = chtObj.BottomRightCell.Offset(-2, 3).Value & vbNewLine

Is that what you needed?

Cheers

Rich

Dear Rich,

Is there anyway I can have the sintex extract any data written on the right of the Chart (J column) without define a specific cell?

Right now I have anything in J7 to J8 and J27 to J29. (Is there anyway to broading that instead of having it pull from a specific cell)

'If the chart is the "US" consumption chart, then enter the appropriate comments

If InStr(activeSlide.Shapes(1).TextFrame.TextRange.Text, "US") Then

activeSlide.Shapes(2).TextFrame.TextRange.Text = Range("J7").Value & vbNewLine

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J8").Value & vbNewLine)

'Else if the chart is the "Renewable" consumption chart, then enter the appropriate comments

ElseIf InStr(activeSlide.Shapes(1).TextFrame.TextRange.Text, "Renewable") Then

activeSlide.Shapes(2).TextFrame.TextRange.Text = Range("J27").Value & vbNewLine

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J28").Value & vbNewLine)

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J29").Value & vbNewLine)

Dear Rich,

I tried this sintex and it looks like it works. I remove paste "special". Do you think this sintex is ok?

'Copy the chart and paste it into the PowerPoint as a Metafile Picture

cht.Select

ActiveChart.ChartArea.Copy

activeSlide.Shapes.Paste.Select

Ian

Dear Rich,

Where would I place this sintex? "ActiveWindow.View.Paste"

   'Copy the chart and paste it into the PowerPoint as a Metafile Picture

cht.Select

ActiveChart.ChartArea.Copy

activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

I'm using MS Office 2010. Would you like me to upload my example over skydrive?

Basically I'll have numerous charts and this vba will just pull all of them an upload it to PPT.

Ian

Hi,

Did you try:

    ActiveWindow.View.Paste

?

Which version of ppt are you using?

Cheers

Rich