Avatar of Member_2_7966563
Member_2_7966563
 asked on

Convert XLS database into PPT Slides, 1 row = 1 Slide

I have an Excel spreadsheet (about 50 rows), and am planning to create a nice Powerpoint template, with preformatted textboxes, fonts, etc.

I would like to transfer the information from the Excel into the PPT. Each row of the Excel will be one slide in the PPT. Each cell will become one text box. All cells in the same column will end up at the same place on various slides, with same font, etc. The final PPT file will contain as many slides as there were rows in the Excel spreadsheet.

Could someone please show me a way?
Microsoft OfficeMicrosoft PowerPointMicrosoft Excel

Avatar of undefined
Last Comment
Jamie Garroch (MVP)

8/22/2022 - Mon
Echo_S

ASKER CERTIFIED SOLUTION
Roy Cox

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jamie Garroch (MVP)

If you want to do it with VBA, then this should get you started:

Option Explicit

' Set to true for IntelliSense and add a reference to Microsoft PowerPoint XX Object Library.
' Delete reference and set to false for production.
#Const DEV_MODE = False

' ==============================================
' Macro to export rows of data from a worksheet to text boxes in PowerPoint (runs from Excel)
' One slide per row, one text box per column
' Dependencies : none
' Tested : Excel and PowerPoint 2016 (PC)
' Source : http://youpresent.co.uk/
' Author : Jamie Garroch
' Date : 19 October 2018
' ==============================================
Sub ExportChartsToPowerPoint()
    ' Declarations for Excel objects
    Dim oWB As Workbook
    Dim oWS As Worksheet
    Dim oRng As Range
    Dim oCell As Excel.Range
    ' Declarations for PowerPoint late-binding objects
#If DEV_MODE Then
    Dim oPPT As PowerPoint.Application
    Dim oPres As PowerPoint.Presentation
    Dim oSld As PowerPoint.Slide
    Dim oShp As PowerPoint.Shape
#Else
    Dim oPPT As Object  ' PowerPoint Application
    Dim oPres As Object ' Presentation
    Dim oSld As Object  ' Slide
    Dim oShp As Object  ' Shape
#End If
  
    ' Module variables
    Dim lRow As Long, lCol As Long
    Dim sShpLeft As Single, sShpTop As Single
    
    ' Constants (change as necessary)
    Const OFFSET_X = 50 ' Offset from the left of the slide
    Const OFFSET_y = 50 ' Offset from the top of the slide
    Const SPACING_X = 50 ' Horizontal space between text boxes
    Const SPACING_y = 50 ' Vertical space between text boxes
    Const TEXTBOX_WIDTH = 100 ' Width of the textboxes
    
    Set oWB = ActiveWorkbook
    ' The next line assumes we are just using sheet 1
    Set oWS = oWB.Worksheets(1)
    ' This line is an alternative to the above and uses the active sheet:
    'Set oWS = oWB.ActiveSheet
    
    ' Try to get an existing instance of PowerPoint application
    Set oPPT = GetObject(, "PowerPoint.Application")
    ' If none exists, create one
    If Err Then Set oPPT = CreateObject("PowerPoint.Application")
    If Err Then MsgBox "Couldn't start PowerPoint.", vbCritical + vbOKOnly, "No PowerPoint": Exit Sub
    
    ' Create a new presentation using the default template set on the system (blank.potx)
    Set oPres = oPPT.Presentations.Add(msoTrue)
    
    ' Set a reference to the used range of cells in the sheet
    Set oRng = oWS.UsedRange
    
    For lRow = 1 To oRng.Rows.Count
      ' Add a new slide to the PowerPoint presentation. Change the CustomLayouts index according to the required layout ID.
      Set oSld = oPres.Slides.AddSlide(oPres.Slides.Count + 1, oPres.SlideMaster.CustomLayouts(1))
      For lCol = 1 To oRng.Columns.Count
        Set oCell = oWS.Cells(lRow, lCol)
        sShpLeft = OFFSET_X + ((lCol - 1) * (SPACING_X + TEXTBOX_WIDTH))
        sShpTop = OFFSET_y
        Set oShp = oSld.Shapes.AddTextbox(msoTextOrientationHorizontal, sShpLeft, sShpTop, TEXTBOX_WIDTH, 0)
        With oShp
          .Line.Visible = msoTrue ' set this to false to hide the text box outline
          With .TextFrame2
            .WordWrap = msoTrue
            .AutoSize = msoAutoSizeShapeToFitText
            '.TextRange = oRng.Cells(lRow, lCol).Shape.TextFrame2.TextRange
            With .TextRange
              .Text = oCell.Value
              With .Font
                .Name = oCell.Font.Name
                .Size = oCell.Font.Size
              End With
            End With
          End With
        End With
      Next
    Next
    
    ' Clear Excel Objects
    Set oWB = Nothing: Set oWS = Nothing: Set oRng = Nothing: Set oCell = Nothing
    ' Clear PowerPoint objects
    Set oPPT = Nothing: Set oPres = Nothing: Set oSld = Nothing: Set oShp = Nothing
End Sub

Open in new window

Member_2_7966563

ASKER
Hi Jamie, What preparation is needed to use this VBA code? Without any preparation, the code stops at line 18 saying User-defined type not defined.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jamie Garroch (MVP)

Lines 3-5 explain this. Unless you need to debug the code, just set the DEV_MODE to False.
Member_2_7966563

ASKER
Does this code go into the Excel or into the PPT?
Jamie Garroch (MVP)

Line 8 states that it "runs from Excel" so you need to put it into the Excel VBE. It could be redeveloped to run the in PowerPoint VBE but when exporting data, I usually write VBA in the app where the data resides.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.