How to Automatically Copy From Excel to PowerPoint Using a VBA Macro

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Automatically Copy From Excel to PowerPoint Using a VBA Macro
Add shapes and text to a PowerPoint slide using VBA. (Hemera Technologies/Photos.com/Getty Images)

If you ever wanted to expedite the transfer of data from a Microsoft Excel workbook to your Microsoft PowerPoint presentation, then using a macro and Visual Basic for Applications is the way to go. VBA is a computer programming language employed in Microsoft Office applications to automate processes such as copying data from Excel. A macro allows you to save a set of instructions that you can execute over and over again with a click of a button.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Launch Excel, type "Alan" in "A1," "Daniel" in "A2," "Kitzia" in "A3," "Oscar" in "A4" and "Yarexli" in "A5." Press "CTRL" and "S" to save the workbook in "C:\" as "ExcelFile.xlsx." Close Excel.

  2. 2

    Launch PowerPoint, click the "Developer" tab and click "Macros" to launch the Macro dialogue window. Type "copyFromExcel" below Macro Name and click the "Create" button. Click the "Tools" menu and click "References" to launch the References dialogue window. Scroll down and check the box next to "Microsoft Excel <version number> Object Library" and click "OK."

  3. 3

    Copy and paste the following to create the variables you will use to copy the data from Excel:

    Dim sourceXL As Excel.Application

    Dim sourcebook As Excel.Workbook
    
    Dim source Sheet As Excel.Worksheet
    
    Dim dataReadArray(10) As String
    
    Dim my Press             As Presentation
    
    Dim new Slide             As Slide
    
  4. 4

    Set values to the object variables:

    Set sourceXL = Excel.Application
    
    Set sourceBook = sourceXL.Workbooks.Open("G:\ExcelFile.xlsx")
    
    Set source Sheet = sourceBook.Sheets(1)
    
    Set myPres = ActivePresentation
    
    Set new Slide = myPres.Slides.Add(Index:=myPres.Slides.Count + 1, Layout:=ppLayoutText)
    
  5. 5

    Read the data in the Excel file and store it in a String array:

    sourceSheet.Range("A1").Select
    
    dataReadArray(0) = sourceSheet.Range("A1").Value
    
    sourceSheet.Range("A2").Select
    
    dataReadArray(1) = sourceSheet.Range("A2").Value
    
    sourceSheet.Range("A3").Select
    
    dataReadArray(2) = sourceSheet.Range("A3").Value
    
    sourceSheet.Range("A4").Select
    
    dataReadArray(3) = sourceSheet.Range("A4").Value
    
    sourceSheet.Range("A5").Select
    
    dataReadArray(4) = sourceSheet.Range("A5").Value
    
  6. 6

    Add the data from the String array to a new slide in your current presentation:

    newSlide.Shapes(1).TextFrame.TextRange = "Data copied from Excel"
    
    newSlide.Shapes(2).TextFrame.TextRange = dataReadArray(0) &amp; vbNewLine &amp; _
    
    dataReadArray(1) &amp; vbNewLine &amp; _
    
    dataReadArray(2) &amp; vbNewLine &amp; _
    
    dataReadArray(3) &amp; vbNewLine &amp; _
    
    dataReadArray(4) &amp; vbNewLine
    
  7. 7

    Close the workbook:

    sourceBook.Close
    
  8. 8

    Switch to the PowerPoint window and click "Macros." Click "Run" to run the "copyFromExcel" macro and add a new slide with the data copied from the Excel file you created in Step 1.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.