Copy excel ranges and paste in PowerPoint slide | debugvba.com

Copy excel range to powerpoint slide vba

Objective

To copy excel range from worksheet to paste or populate on a PowerPoint slide.

Steps

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.

Steps

  1. Open PowerPoint presentation using dialog box from a excel macro file
  2. Loop through each worksheet, copy the data and paste it to slides
  3. Change the height, width, distance from top and left of pasted range
  4. Move to next worksheet, we shall paste the data in the next slide
  5. Save and close the workbook

Code

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.

Copy excel range to powerpoint slide vba

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.

Copy excel range to powerpoint slide vba

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.

Copy excel range to powerpoint slide vba

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.

Copy excel range to powerpoint slide vba

Notes

  1. We can copy other objects like charts,smarts shapes, autoshape or any other object by following similar approach.
  2. We can add the error handling code as per requirement.

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.shape.height
https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shapes.pastespecial

 

Post you may like

Add leading zeros in a number – VBA Excel formatting | debugvba.com