VBA Macro to Transfer Data from Excel to PowerPoint

PowerPoint / By /

Transferring data from Excel to PowerPoint using VBA can be very useful for automating reporting and presentations. This comprehensive guide will walk through the key steps to set up the VBA macro and provide code examples for transferring data.

Getting Started with VBA

To use VBA (Visual Basic for Applications) to transfer Excel data to PowerPoint, you need to:

  1. Enable Developer Tab in Excel – Go to File > Options > Customize Ribbon and check ‘Developer’ to enable this tab
  2. Enable PowerPoint Library Reference – In the VBA editor (ALT+F11), go to Tools > References and enable “Microsoft PowerPoint xx.x Object Library”
  3. Set PowerPoint Application Variable – Declare a PowerPoint application variable in VBA to launch PowerPoint and create presentations
Dim PowerPointApp As PowerPoint.Application
Set PowerPointApp = New PowerPoint.Application

Transfer Excel Range

To transfer an Excel range to PowerPoint, follow these steps:

  1. Select the range in Excel to copy, for example: vb Set rng = Worksheets("Sheet1").Range("A1:C10")
  2. Copy the Excel range rng.Copy
  3. Paste range in PowerPoint and position as needed: PowerPointApp.ActivePresentation.Slides(1).Shapes.Paste.Select PowerPointApp.ActiveWindow.Selection.ShapeRange _ .Left = 10 .Top = 10

Transfer Excel Chart

To copy an Excel chart to PowerPoint:

  1. Declare chart object variable Dim chrt As Chart
  2. Set variable to chart name vb Set chrt = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
  3. Copy and paste chart in PowerPoint chrt.Copy PowerPointApp.ActivePresentation.Slides(1).Shapes.Paste.Select

Transfer Worksheets as Slides

To transfer each Excel worksheet as slides in PowerPoint:

For Each sh In ThisWorkbook.Worksheets
    sh.Copy
    Set ppSlide = PowerPointApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
    ppSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
Next sh

This loops through all worksheets and pastes each one into a new slide.

Example Code

Below is an full VBA code example to transfer data from Excel to PowerPoint:

Sub ExcelToPowerPoint()

    'Create PowerPoint Application
    Dim PowerPointApp As Object
    Set PowerPointApp = CreateObject("PowerPoint.Application")

    'Copy Excel Range
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:D10")
    rng.Copy

    'Paste Range in PowerPoint
    PowerPointApp.Presentations.Add
    PowerPointApp.ActivePresentation.Slides(1).Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
    PowerPointApp.Visible = True

    'Copy Excel Chart
    Dim chrt As Chart
    Set chrt = ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    chrt.Copy

    'Paste Chart in new slide
    PowerPointApp.ActivePresentation.Slides.Add(2, 1) 
    PowerPointApp.ActivePresentation.Slides(2).Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile

    'Transfer each Worksheet into Slide
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        sh.Copy
        Set ppSlide = PowerPointApp.ActivePresentation.Slides.Add(1, ppLayoutBlank) 
        ppSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile 
    Next sh

End Sub

This provides a template to adapt for your specific Excel data transfer requirements.

Additional Tips

Here are some additional tips when transferring Excel data to PowerPoint with VBA:

  • Use error handling with On Error statements to avoid failures interrupting code execution
  • Set PowerPointApp.Visible = True to view the PowerPoint when it opens
  • The PasteSpecial method provides options like formatting and layouts
  • Charts can also be created directly in PowerPoint without copying from Excel

Conclusion

With this comprehensive VBA guide and code examples, you should have all the building blocks to successfully automate transferring data from Excel workbooks over to PowerPoint. This allows creating dynamic reporting and presentations while avoiding tedious manual copy-paste work.

The key is setting up the PowerPoint object references, declaring your source data ranges/charts, and then programmatically instructing VBA to copy and paste to desired slides with the appropriate formatting. Feel free to adapt the template code above into reusable macros and functions within your Excel VBA projects.

Related Posts