To copy excel range from worksheet to paste or populate on a PowerPoint slide.
In this VBA Excel automation, we are copying the excel ranges from 3 worksheets to a separate PowerPoint slides.Here we have got the data in the excel macro file (xlsm), which we want to paste this data into PowerPoint presentation slides. We loop through each worksheet in the macro file. Then we copy the data from the worksheet and paste it into PowerPoint slide. Data from each worksheet is pasted into different slides.
- Open PowerPoint presentation using dialog box from a excel macro file
- Loop through each worksheet, copy the data and paste it to slides
- Change the height, width, distance from top and left of pasted range
- Move to next worksheet, we shall paste the data in the next slide
- Save and close the workbook
Sub CopyMultipleChartsFromExcelToPpt() Application.ScreenUpdating = False Dim obPptApp As PowerPoint.Application Dim OpenPptDialogBox As Object Dim CopiedRange As Object Dim ws As Worksheet Dim SlideIndex As Integer Set obPptApp = CreateObject("PowerPoint.Application") Set OpenPptDialogBox = obPptApp.FileDialog(msoFileDialogOpen) 'Open the target PPT using dialog box obPptApp.Activate If OpenPptDialogBox.Show = -1 Then obPptApp.Presentations.Open (OpenPptDialogBox.SelectedItems(1)) End If SlideIndex = 1 'We are copying the charts from 3 separate worksheets, and pasting it in a 3 separate slides For Each ws In ThisWorkbook.Sheets 'Copy and paste excel range to ppt slide ws.Select ws.Range("A1:B3").Copy obPptApp.ActiveWindow.Presentation.Slides(SlideIndex).Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse SlideShapeCount = obPptApp.ActiveWindow.Presentation.Slides(SlideIndex).Shapes.Count 'Change the position from left and top, height and width of copied range Set CopiedRange = obPptApp.ActiveWindow.Presentation.Slides(SlideIndex).Shapes(SlideShapeCount) CopiedRange.Height = 250 CopiedRange.Width = 250 CopiedRange.Left = 180 CopiedRange.Top = 150 SlideIndex = SlideIndex + 1 Next ws 'Save and close the file obPptApp.ActivePresentation.Save obPptApp.Windows(1).Close End Sub
First we shall add the required libraries as shown in the image below.
We have created a command button on the excel macro file. VBA code is written in this macro file for demonstration purpose.We have 3 worksheets in the sample macro file, and each of them had the data in A1:B3 cells.
When we click on the command button, a dialog box will be displayed.We shall select a target PowerPoint file on which excel range will be pasted.We are selecting the ppt file using dialog box in the image shown below.
We had added some dummy data in Range(“A1:B3”) in all the 3 worksheet, with the help of this VBA code, we have copied the data from excel cell to PowerPoint slide.When the data is pasted on .ppt file , it retains the source data formatting as we can see in the image below.
- We can copy other objects like charts,smarts shapes, autoshape or any other object by following similar approach.
- We can add the error handling code as per requirement.
Post you may like