Convert Excel cells into PPT Slide Textboxes

Member_2_7966563 used Ask the Experts™
I have a 9 ideas spread over three tabs of an Excel spreadsheet, three rows each. Please see attached Excel file example.
I would like to convert them into a single slide PPT, please see attached PPT file example.

How could I convert the source Excel file into the destination PPT slide with minimal manual work?Source.xlsx
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Technical Consultant at BrightCarbon
This is tested and working with the Excel file you provided:

Option Explicit

' Change to true to use IntelliSense and add a reference to the Microsoft PowerPoint XX Object Library
' Change to false for production and remove the reference to the Microsoft PowerPoint XX Object Library
#Const DEV_MODE = False

' ===========================================================================
' Macro to create a presentation from the data in 3 sheets of an Excel file
' Target : Excel VBE
' Author : Jamie Garroch of
' Date : 28NOV2018
' ===========================================================================
Sub CreatePresentation()
  ' Early bound PowerPoint objects (requires reference to PowerPoint library)
  Dim oPPT As PowerPoint.Application
  Dim oPres As PowerPoint.Presentation
  Dim oSld As PowerPoint.slide
  Dim oShp As PowerPoint.Shape
  ' Late bound PowerPoint objects (no reference required)
  Dim oPPT As Object
  Dim oPres As Object
  Dim oSld As Object
  Dim oShp As Object
#End If
  ' Excel objects
  Dim oWS As Worksheet
  ' PowerPoint textbox layout variables
  Dim lRow As Long
  Dim lCol As Long
  ' PowerPoint textbox layout constants
  Const ShpWidth = 135.9184
  Const ShpHeight = 80.08165
  Const xOffset = 238.7755
  Const yOffset = 114.6122
  Const xSpacing = 19.10205
  Const ySpacing = 35.26527
  On Error Resume Next
  ' Use an existing instance of PowerPoint, if it exists
  Set oPPT = GetObject(, "PowerPoint.Application")
  ' If PowerPoint isn't running, start it
  If Err Then
    Set oPPT = CreateObject("PowerPoint.Application")
    If Err Then MsgBox "Couldn't start PowerPoint.", vbCritical + vbOKOnly, "PowerPoint Error": Exit Sub
  End If
  ' Create a new presentation (using the default template set on the user's machine e.g. blank.potx)
  Set oPres = oPPT.Presentations.Add
  ' Change the "1" in "CustomLayouts(7)" to the index of the slide layout you need in your PowerPoint template
  ' 7 = Blank layout in Destination.pptx
  Set oSld = oPres.Slides.AddSlide(1, oPres.SlideMaster.CustomLayouts(7))
  ' Create the 9 textbox shapes, referring to the data in the sheets as follows:
  ' SheetX = RowX, ColY = SheetX/RowY+1
  With oSld.Shapes
    For lRow = 1 To 3
      Set oWS = ActiveWorkbook.Worksheets(lRow)
      For lCol = 1 To 3
        Set oShp = oSld.Shapes.AddShape(msoShapeRectangle, xOffset + (lCol - 1) * (ShpWidth + xSpacing), yOffset + (lRow - 1) * (ShpHeight + ySpacing), ShpWidth, ShpHeight)
        With oShp.TextFrame.TextRange
          ' Copy the text from Excel to the text box and format it as required
          .Text = oWS.Cells(lCol + 1, 1) & vbCrLf & oWS.Cells(lCol + 1, 2)
          .Font.Size = 8
          ' Format the text box title text
          With .Paragraphs(1).Font
            .Size = 18
            .Bold = msoTrue
          End With
        End With
  End With

End Sub

Open in new window

Sam JacobsDirector of Technology Development, IPM



Thanks Jamie, your solution worked straight out of the box

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial