How to Copy a Chart From Excel to PowerPoint With VBA
Presentations are an important part of many business conferences and meetings, and companies all across the globe use Microsoft PowerPoint to build and display their slideshows. While PowerPoint allows you to create interactive charts in slides, Microsoft Excel allows you to create and manipulate chart data in many more ways. Therefore, using Excel to first create your charts and then importing them into PowerPoint allows you to create presentations with chart data that is more accurate. Using Visual Basic for Applications you can insert Excel charts into a new PowerPoint presentation programmatically rather than using the cumbersome copy-and-paste method.
Open Excel on your computer. Open or create the Excel workbook with a chart or multiple charts that you want to export to a PowerPoint presentation.
Click the “File” link on the Excel ribbon and then click “Options” on the drop-down list. After the Excel Options window appears, click the “Customize Ribbon” label on the left side.
Click and enable the “Developer” option under the Main Tabs header. Click “OK” to save the ribbon changes and return to the spreadsheet window in Excel.
Click “Developer” on the ribbon and then click “Insert” in the Controls section. Click the “Button Control” icon under the Form Controls header.
Click the cell where you want to create a macro button for exporting your chart to a new PowerPoint presentation. After you click the selected cell, the Assign Macro window appears on the screen.
Type a descriptive name for the macro in the “Macro Name” field. Enter “CopyChartToPP” or something similar. Ensure that there are no spaces in the macro name or it will not save properly.
Click the “New” button. The VBA editor opens and displays a blank VBA Module code window. Copy and paste the following code in the code window between the “Sub” and “End Sub” syntax (note the comments in the code to make it easier to customize if you need to do so):
'Declare the needed variables Dim newPP As PowerPoint.Application Dim currentSlide As PowerPoint.Slide Dim Xchart As Excel.ChartObject ‘Check if PowerPoint is active On Error Resume Next Set newPP = GetObject(, "PowerPoint.Application") On Error GoTo 0 'Open PowerPoint if not active If newPP Is Nothing Then Set newPP = New PowerPoint.Application End If ‘Create new presentation in PowerPoint If newPP.Presentations.Count = 0 Then newPP.Presentations.Add End If 'Display the PowerPoint presentation newPowerPoint.Visible = True 'Locate Excel charts to paste into the new PowerPoint presentation For Each Xchart In ActiveSheet.ChartObjects 'Add a new slide in PowerPoint for each Excel chart newPP.ActivePresentation.Slides.Add newPP.ActivePresentation.Slides.Count + 1, ppLayoutText newPP.ActiveWindow.View.GotoSlide newPP.ActivePresentation.Slides.Count Set currentSlide = newPP.ActivePresentation.Slides(newPP.ActivePresentation.Slides.Count) 'Copy each Excel chart and paste it into PowerPoint as an Metafile image Xchart.Select ActiveChart.ChartArea.Copy currentSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select 'Copy and paste chart title as the slide title in PowerPoint currentSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text 'Adjust the slide position for each chart slide in PowerPoint. Note that you can adjust the values to position the chart on the slide to your liking newPP.ActiveWindow.Selection.ShapeRange.Left = 25 newPP.ActiveWindow.Selection.ShapeRange.Top = 150 currentSlide.Shapes(2).Width = 250 currentSlide.Shapes(2).Left = 500 Next AppActivate ("Microsoft PowerPoint") Set currentSlide = Nothing Set newPP = Nothing
Click the “Save” icon button on the VBA editor toolbar. Click “Tools” on the menu bar and then click “References.” Scroll down to and click the check box next to the “Microsoft PowerPoint 14.0 Object Library” reference. Click the “OK” button to save the external reference.
Click the “Save” icon on the VBA editor toolbar again. Close the VBA editor window.
Right-click the new command button in the spreadsheet window. Change the label text for the button to “Export Charts to PP” or something similar.
Click “File” on the ribbon and then click “Save As.” Enter a new name for the Excel file and save it as an Excel Macro-Enabled Template.
Click the new command button in the spreadsheet window. Excel runs that VBA code, which then opens PowerPoint and copies all charts in the spreadsheet as images on individual slides in the new presentation.
- MSDN Library: Getting Started with VBA in Office 2010
- Peltier Technical Services, Inc.: Using Excel with Other Office Applications
- ExcelTip.com: Basic Information About OLE Automation Using VBA in Microsoft Excel
- Chandoo.org: Create PowerPoint Presentations Automatically Using VBA
- MSDN Library: Inserting Excel Charts into PowerPoint 2007
- If you save the Excel workbook as a standard spreadsheet file, the macro code will not work. Therefore, ensure that you save the workbook as a macro-enabled file type.
Jeff Grundy has been writing computer-related articles and tutorials since 1995. Since that time, Grundy has written many guides to using various applications that are published on numerous how-to and tutorial sites. Born and raised in South Georgia, Grundy holds a Master of Science degree in mathematics from the Georgia Institute of Technology.