VBA code to copy data into powerpoint 2010 chart from excel 2010?

[josephjcota]

VBA code to copy data into powerpoint 2010 chart from excel 2010?
I am trying to automate the process of populating the charts in the powerpoint (office 2010). Can anybody please help with the VBA code to

Copy data in excel > select the template chart in the powerpoint > open the data sheet > paste the copied data and close>loop

I am using office 2010, so please provide codes that work in office 2010.

I initially tried to create charts in excel and paste it in the powerpoint with the link and update the link whenever we get new data. However, after pasting couple of charts it becomes very slow. Since I have more than 200 charts, updating all the charts using VBA is my only option.

 

[nmoiseff]

Disregard. I was able to figure it out. Updated code below. Thank you very much again for your help.


lngSlideKount = 0
For Each ws In ActiveWorkbook.Worksheets
'ppLayoutBlank = 12
Set pptSld = pptPres.Slides.Add(lngSlideKount + 1, 12)
pptApp.ActiveWindow.View.GotoSlide pptSld.SlideIndex
' ======================================================================
' Changed code in this Message Board post:
' This code needs to be tidied
' e.g. Dim wb as Workbook
' Set wb = ThisWorkbook
' Set ws = wb.Worksheets("MySheet")
' etc.
' ======================================================================
' select sheet containing data to be copied to PowerPoint:
'Sheets("MySheet").Select
ws.Activate '<==========================================
' select cells to be copied to PowerPoint:
'Range("A1:I24").Select
Range("I1:Y35").Select

' copy cells:
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
' past to PowerPoint:
pptSld.Shapes.Paste.Select
lngSlideKount = lngSlideKount + 1
' END OF CHANGED CODE
' ======================================================================

pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'
Next ws

 

[Derek Brown]

Well done. I was in the process of replying to suggest making that change although the code worked for me with the 'select'.

 

[ffialho]

Hi all! I've been using this code! It's great! Just need to know how I can referente to open a specific PPT instead of creating a new instance. And How can I resize the picture in PPT?! can you put out an example so I can complete my magic here. thanks

 

[jjbungles]

Hello,

I have done a macro, based in excel 2003 that does what i think you want.


It's here:

http://www.mrexcel.com/forum/showthread.php?p=2933602#post2933602


JJ

 

[hitesh.relhan]

I have been asked to provide a code change to 'link' the excel charts to PowerPoint instead of coping the charts as pictures.
Here is the code to replace the "similar" section of code previously given:

    lngSlideKount = 0
    For Each ws In ActiveWorkbook.Worksheets
      'Verify if there is a chart object to transfer
      If ws.ChartObjects.Count > 0 Then
        For Each objChartObject In ws.ChartObjects
          Set objChart = objChartObject.Chart
          'ppLayoutBlank = 12
          Set pptSld = pptPres.Slides.Add(lngSlideKount + 1, 12)
          pptApp.ActiveWindow.View.GotoSlide pptSld.SlideIndex
          With objChart
            '
            objChart.ChartArea.Copy '<=====
            pptSld.Shapes.PasteSpecial(Link:=msoCTrue).Select  ' <=====
            '
            pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
            pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
          End With
          lngSlideKount = lngSlideKount + 1
        Next objChartObject
      End If
    Next ws
    ' Now check CHART sheets:
    For Each objCht In ActiveWorkbook.Charts
        'ppLayoutBlank = 12
        Set pptSld = pptPres.Slides.Add(lngSlideKount + 1, 12)
        pptApp.ActiveWindow.View.GotoSlide pptSld.SlideIndex
        With objCht
            '
            .ChartArea.Copy ' <=====
            pptSld.Shapes.PasteSpecial(Link:=msoCTrue).Select  ' <=====
            '
            pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
            pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
        End With
        lngSlideKount = lngSlideKount + 1
    Next objCht

The changed lines are highlighted with "<=====".

This seems to work OK for me using Office 2010 but note that it has not been extensively tested.

Hi Derek

I tried using this code. But I got an error due to the line

' You need to add a reference (Tools | References) to the Microsoft PowerPoint nn.nn Object Library Dim pptApp As PowerPoint.Application


Please let me know how can we solve this error.
I am a novice in vba. Help would be highly appreciated.

 

[Joe4]

I tried using this code. But I got an error due to the line

' You need to add a reference (Tools | References) to the Microsoft PowerPoint nn.nn Object Library Dim pptApp As PowerPoint.Application


Please let me know how can we solve this error.
I am a novice in vba. Help would be highly appreciated.

The code uses Microsoft PowerPoint objects, so you need to select that object library in order to use it.

Just go into your VB Editor, and from the Tools menu, select References. Then browse down the list under you find one that begins with "Microsoft PowerPoint ...", select it (check-box), and click OK.

 

[JPCMcAllen]

Derek, thanks for the code it works great but I'm fighting to have the correct template applied to each slide created with the code above and now several years later the link you posted is not working. Do you or does anyone have some code they would share?

Using Office Professional 2010 I'd like to have a Template applied to each slide created and a 2nd bonus would hint at copying each existing Pivot table to a slide in the same manner as the charts, pasting as an image. Thanks in advance to all. GREAT forum. GREAT resource.

 

[meghbal]

I have 200 excel cells a1-a200

I need to creat 200 power point slides can you help me.

My excell File is "Morry"

and the power point is "Lesson1"

Please, give me simple instructions.
it is all text.

 

[JPCMcAllen]

I'm still trying to find a way to apply a specific Power Point Template in this process/Routine that runs from Excel. Any help would be appreciated. Thanks

 

[meghbal]

I was not able to do it.