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

josephjcota

New Member
Joined
Aug 19, 2011
Messages
1
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.
 
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
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Well done. I was in the process of replying to suggest making that change although the code worked for me with the 'select'.
 
Upvote 0
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
 
Upvote 0
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:
Code:
    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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top