We help IT Professionals succeed at work.

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

124 Views
Last Modified: 2020-11-13
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?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Group Finance Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

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

Author

Commented:
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.
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

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

Author

Commented:
Does this code go into the Excel or into the PPT?
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions