macro to copy chart from excel to powerpoint

Question

is there a way i can get a macro to copy a chart from excel into power point? I know how to do it for tables within the actual sheet, but cannot seem to get the chart to copy as i cannot use cell ranges.

Thanks

Answer 1

Dear Rich,

This looks great. 

I'm running into an error "Method 'Offset' of object 'Range' failed" error# '-2147417848 (80010108)'

The info.(notes) are next to the charts and it would be designated to colunm J (infinite rows) only.

Should it be this?

        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 = chtObj.BottomRightCell.Offset(-2, 3).Value & vbNewLine

            End If

Or 

Since I want to pull all info. that is next to the each chart.

'Pull all notes next to the charts

        With pptShpPH.TextFrame.TextRange

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

Answer 2

BTW Thank you for you help

Answer 3

Dear Rich,

This looks great. 

I'm running into an error "Method 'Offset' of object 'Range' failed" error# '-2147417848 (80010108)'

The info.(notes) are next to the charts and it would be designated to colunm J (infinite rows) only.

           

Should it be this?

        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 = chtObj.BottomRightCell.Offset(-2, 3).Value & vbNewLine

            End If

Or Since I want to pull all info. that is next to the each chart.

         'Pull all notes next to the charts

        With pptShpPH.TextFrame.TextRange

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

Hi,

OK, I don't know exactly how your sheet looks.  The two numbers in the Offset() are rows and columns offsets.  So

     chtObj.BottomRightCell.Offset(-2, 3)

is the cell 2 rows up and 3 columns to the right of the cell where the bottom-right corner of the chart is.

You would need to ensure that all of your charts are positioned in a consistent way relative to the data you want to extract if you want to use this method.

I suspect the error you have seen is because -2 rows takes you off the top of the worksheet????

It is up to you to determine which cells you want under which circumstances.  If you need to do something different if the title contains "US", use the "If" test you have, if you don't care about that then you don't need to use it.

Hope that helps.

Cheers

Rich

Answer 4

Dear Rich,

Here is a copy of my excel on skydrive <iframe src="https://skydrive.live.com/embed?cid=1B7A1F7F35D8435F&resid=1B7A1F7F35D8435F%21178&authkey=AP9gwSNLO3n7Pt4" width="98" height="120" frameborder="0" scrolling="no"></iframe>

You are correct I'm planning to only input notes in column J only.

Hopefully this gives you a better idea of it.

Answer 5

Hi,

Got it.

At the top of your macro, add:

    Dim rng As Excel.Range, c As Excel.Range

then change the bottom part of your macro as follows:

        With pptShpPH.TextFrame.TextRange

            'Get the text to the right of the chart:

            Set rng = Range(Cells(chtObj.TopLeftCell.Row, "J"), Cells(chtObj.BottomRightCell.Row, "J"))

            For Each c In rng    'loop through each cell in column J next to the chart...

                If c.Value <> "" And c.Value <> "Callouts:" Then

                    .InsertAfter (c.Value & vbNewLine)

                End If

            Next c

            'Change the font size of the PlaceHolder:

            .Font.Size = 16

        End With

    Next chtObj

Make sense?

Also, when I tested your code, I already had ppt open with some other presentations.  Your line:

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

meant that your charts were added to the end of an existing presentation (oops!).  Maybe that's what you want, but unless you want to have to be in control of what presentations are open, I would remove the If test from that line and just add a new presentation each time  (ppt.Presentations.Add).

Hope that helps.

Cheers

Rich