Convert Excel cells into PPT Slide Textboxes

Member_2_7966563
Member_2_7966563 used Ask the Experts™
on
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
Destination.pptx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Technical Consultant at BrightCarbon
Commented:
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 http://brightcarbon.com
' Date : 28NOV2018
' ===========================================================================
Sub CreatePresentation()
#If DEV_MODE Then
  ' 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
#Else
  ' 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
    Err.Clear
    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
      Next
    Next
  End With

End Sub

Open in new window

Sam JacobsDirector of Technology Development, IPM

Commented:
Nice!

Author

Commented:
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