• Creator
  • #2270151

    VBA Macro to transfer data fro excel to Powerpoint


    by breeze_rathna ·

    I need to present a chart with the data in Excel dynamically using VBA Macro

All Answers

  • Author
    • #2600493


      by breeze_rathna ·

      In reply to VBA Macro to transfer data fro excel to Powerpoint


    • #2613005

      me to

      by tyler.poland ·

      In reply to VBA Macro to transfer data fro excel to Powerpoint

      did you ever figure out how?

    • #2616843

      Simple Solution

      by rich ·

      In reply to VBA Macro to transfer data fro excel to Powerpoint

      For a quick and simple solution check out the tool at:

    • #2920889

      Run an excel macro from within PowerPoint

      by jyellis ·

      In reply to VBA Macro to transfer data fro excel to Powerpoint

      This is how I do it:

      Sub XLTest()
      Dim XL as Object

      Set XL = CreateObject “Excel.Application”)

      XL.Workbooks.Open “C:\My Documents\ExcelFile.xls”

      ‘ If there is more than one macro called TestMacro,
      ‘ the module name would be required as in

      ‘ XL.Run “Module1.TestMacro”

      ‘ to differentiate which routine is being called.

      XL.Run “LoopShts”

      End Sub

      Sub Copy_Paste_to_PowerPoint()

      ‘Requires a reference to the Microsoft PowerPoint Library via the Tools – Reference menu in the VBE
      Dim ppApp As PowerPoint.Application
      Dim ppSlide As PowerPoint.Slide

      ‘Original code sourced from Jon Peltier
      ‘This code developed at

      Dim SheetName As String
      Dim TestRange As Range
      Dim TestSheet As Worksheet
      Dim TestChart As ChartObject

      Dim PasteChart As Boolean
      Dim PasteChartLink As Boolean
      Dim ChartNumber As Long

      Dim PasteRange As Boolean
      Dim RangePasteType As String
      Dim RangeName As String
      Dim AddSlidesToEnd As Boolean
      Dim shts As Worksheet

      ‘SheetName – name of sheet in Excel that contains the range or chart to copy

      ‘PasteChart -If True then routine will copy and paste a chart
      ‘PasteChartLink -If True then Routine will paste chart with Link; if = False then paste chart no link
      ‘ChartNumber -Chart Object Number

      ‘PasteRange – If True then Routine will copy and Paste a range
      ‘RangePasteType – Paste as Picture linked or unlinked, “HTML” or “Picture”. See routine below for exact values
      ‘RangeName – Address or name of range to copy; “B3:G9” “MyRange”
      ‘AddSlidesToEnd – If True then appednd slides to end of presentation and paste. If False then paste on current slide.

      ‘use active sheet. This can be a direct sheet name

      SheetName = ActiveSheet.Name

      ‘Setting PasteRange to True means that Chart Option will not be used
      PasteRange = True
      RangeName = (“A3:e5″) ‘”MyRange”
      RangePasteType = “Picture”
      RangeLink = True

      PasteChart = False
      PasteChartLink = True
      ChartNumber = 1

      AddSlidesToEnd = True

      ‘Error testing
      On Error Resume Next
      Set TestSheet = Sheets(SheetName)
      Set TestRange = Sheets(SheetName).Range(RangeName)
      Set TestChart = Sheets(SheetName).ChartObjects(ChartNumber)
      On Error GoTo 0

      If TestSheet Is Nothing Then
      MsgBox “Sheet ” & SheetName & ” does not exist. Macro will exit”, vbCritical
      Exit Sub
      End If

      If PasteRange And TestRange Is Nothing Then
      MsgBox “Range ” & RangeName & ” does not exist. Macro will exit”, vbCritical
      Exit Sub
      End If

      If PasteRange = False And PasteChart And TestChart Is Nothing Then
      MsgBox “Chart ” & ChartNumber & ” does not exist. Macro will exit”, vbCritical
      Exit Sub
      End If

      ‘Look for existing instance
      On Error Resume Next
      Set ppApp = GetObject(, “PowerPoint.Application”)
      On Error GoTo 0

      ‘Create new instance if no instance exists
      If ppApp Is Nothing Then Set ppApp = New PowerPoint.Application
      ‘Add a presentation if none exists
      If ppApp.Presentations.Count = 0 Then ppApp.Presentations.Add

      ‘Make the instance visible
      ppApp.Visible = True

      ‘Check that a slide exits, if it doesn’t add 1 slide. Else use the last slide for the paste operation
      If ppApp.ActivePresentation.Slides.Count = 0 Then
      Set ppSlide = ppApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
      If AddSlidesToEnd Then
      ‘Appends slides to end of presentation and makes last slide active
      ppApp.ActivePresentation.Slides.Add ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
      ppApp.ActiveWindow.View.GotoSlide ppApp.ActivePresentation.Slides.Count
      Set ppSlide = ppApp.ActivePresentation.Slides(ppApp.ActivePresentation.Slides.Count)
      ‘Sets current slide to active slide
      Set ppSlide = ppApp.ActiveWindow.View.Slide
      End If
      End If

      ‘Options for Copy & Paste Ranges and Charts
      If PasteRange = True Then
      ‘Options for Copy & Paste Ranges
      If RangePasteType = “Picture” Then
      ‘Paste Range as Picture
      ppSlide.Shapes.PasteSpecial(ppPasteDefault, link:=RangeLink).Select
      ‘Paste Range as HTML
      ppSlide.Shapes.PasteSpecial(ppPasteHTML, link:=RangeLink).Select
      End If
      ‘Options for Copy and Paste Charts
      If PasteChartLink = True Then
      ‘Copy & Paste Chart Linked
      ‘Copy & Paste Chart Not Linked
      ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
      End If
      End If

      ‘Center pasted object in the slide
      ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
      ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

      AppActivate (“Microsoft PowerPoint”)
      Set ppSlide = Nothing
      Set ppApp = Nothing

      End Sub


      Sub loopShts()

      Dim shts As Worksheet

      For Each shts In ActiveWorkbook.Worksheets
      If shts.Name = “Summary” Then
      GoTo NoSummary
      If shts.Name Like “*” & “Summary” Then
      Call Copy_Paste_to_PowerPoint
      GoTo NoSummary
      End If
      End If
      Next shts

      End Sub

      • #2999680


        by gs 059 ·

        In reply to Run an excel macro from within PowerPoint

        I am trying to follow what is in post 4, but I am getting stuck at XL.Run “LoopShts”, I keep getting an error saying it cannot run the macro “LoopShts”, but I do have the sub in the same module 1. I am using 2007 if that makes a difference..

      • #2826980

        Excel macro to ppt

        by parinits ·

        In reply to Run an excel macro from within PowerPoint

        I am in need of your help.

        I am currently working on a requirement where the excel shoud automate powerpoint..

        For eg
        Jan Feb Mar
        East 10 30 40
        West 13 14 45
        South 34 35 33

        Each row in the excel should generate a seperate slide.
        So as per the data we r supposed to have 3 slides..

        I was able to code in such a way it works for a single slide and get updated automatically when the months grows
        But i am not able to get for more than one slide.Please can you give me a clue to continue..

        I am really struck with this..
        My code is pastes below written in MS xl 2003
        Sub Chart2PPT()
        Dim objPPT As Object
        Dim objPrs As Object
        Dim objGraph As Object
        Dim objDataSheet As Object
        Dim rngData As Range
        Dim intRow As Integer
        Dim intCol As Integer

        ‘ excel chart data
        Set rngData = Range(“A1:J2”)
        ‘ open powerpoint
        Set objPPT = CreateObject(“Powerpoint.application”)
        objPPT.Visible = True
        ‘ existing powerpoint pres
        objPPT.Presentations.Open “C:\PPt\Call_volume.ppt”
        ‘ chart on slide 2
        Set objPrs = objPPT.Presentations(1).slides(2)
        ‘ pointer to graph
        Set objGraph = objPrs.Shapes(2).OLEFormat.Object.Application
        ‘ pointer to graphs data sheet
        Set objDataSheet = objGraph.Datasheet
        ‘ transfer data
        For intRow = 1 To rngData.Rows.Count
        For intCol = 1 To rngData.Columns.Count
        objDataSheet.Cells(intRow, intCol) = rngData.Cells(intRow, intCol)
        ‘ update to keep changes

        ‘ tidy up objects
        Set rngData = Nothing
        Set objGraph = Nothing
        Set objDataSheet = Nothing
        Set objPrs = Nothing
        Set objPPT = Nothing
        End Sub

        • #3032696

          Why not use EzPaste?

          by aviben ·

          In reply to Excel macro to ppt


          You should maybe consider using EzPaste, a tool that completely automates the transfer from Excel to PowerPoint and more (

        • #2858294

          ever heard of a do loop?

          by saoidjf456434 ·

          In reply to Excel macro to ppt

          Do while x<4
          'stuff here ex: slides(x)

    • #2811904

      help required

      by mhin ·

      In reply to VBA Macro to transfer data fro excel to Powerpoint

      i need a macro which can copy any form of data on excel into power point additionally there should be a button on excel sheet by pressing which the macro runs
      please if some body can help me as soon as possible

Viewing 4 reply threads