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

by Jaime Avelar
Add shapes and text to a PowerPoint slide using VBA.

Add shapes and text to a PowerPoint slide using VBA.

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.

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.

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

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 sourceSheet As Excel.Worksheet Dim dataReadArray(10) As String Dim myPress As Presentation Dim newSlide As Slide

Set values to the object variables: Set sourceXL = Excel.Application Set sourceBook = sourceXL.Workbooks.Open("G:\ExcelFile.xlsx") Set sourceSheet = sourceBook.Sheets(1) Set myPres = ActivePresentation Set newSlide = myPres.Slides.Add(Index:=myPres.Slides.Count + 1, Layout:=ppLayoutText)

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

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) & vbNewLine & _ dataReadArray(1) & vbNewLine & _ dataReadArray(2) & vbNewLine & _ dataReadArray(3) & vbNewLine & _ dataReadArray(4) & vbNewLine

Close the workbook: sourceBook.Close

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.

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.

Photo Credits

  • photo_camera Hemera Technologies/Photos.com/Getty Images