Extract Powerpoint Textboxes into Excel

Member_2_7966563 used Ask the Experts™
I have a powerpoint deck with 50 slides. All slides have the same template, and consists of textboxes containing various pieces of information, like a form.

How can I extract all that information into an Excel spreadsheet, one row representing one slide, and one column representing one text box?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

May be this can help you?

Open the embedded info in Excel and save it to a new file:

    Rightclick the chart or worksheet data in PowerPoint
    Choose Chart Object, Open from the pop-up menu. Excel opens with the content loaded.
    Choose File, Save Copy As ...
    Give the new filel a name and save.
    Close Excel.

Or you want to create VBA-macros to do this operation?
Senior Technical Consultant at BrightCarbon
Here's a VBA solution that you can adapt to your needs:

' ==========================================
' Module : PowerPointTextBoxesToExcel
' VBE : PowerPoint
' ==========================================

Option Explicit

' Set to true to use IntelliSense
' True requires adding a Reference to Microsoft Excel XX.X Object Library
#Const DevMode = False

' ===============================================================================
' PowerPoint Macro
' Purpose: Copies the text from textbox shapes in the active presentation to the
'          active sheet in the active workbook in Excel.
' Author: Jamie Garroch of YOUpresent.co.uk
' ===============================================================================
Sub CopyTextFromPowerPointToExcel()
#If DevMode Then
  ' Early bound Excel objects
  Dim oXL As Excel.Application
  Dim oWB As Workbook
  Dim oWS As Worksheet
  ' Late bound Excel objects
  Dim oXL As Object
  Dim oWB As Object
  Dim oWS As Object
#End If
  ' PowerPoint objects
  Dim oPres As Presentation
  Dim oSld As Slide
  Dim oShp As Shape

  Dim lRow As Long, lCol As Long  ' variables for Excel cell reference
  On Error Resume Next
  ' Get a reference to the Excel instance
  Set oXL = GetObject(, "Excel.Application")
  If oXL Is Nothing Then Set oXL = CreateObject("Excel.Application")
  If oXL Is Nothing Then
    MsgBox "Couldn't find/open Excel.", vbCritical + vbOKOnly, "Excel not open"
    Exit Sub
  End If
  oXL.Visible = True
  ' Get a reference to the active workbook in Excel
  Set oWB = oXL.ActiveWorkbook
  If oWB Is Nothing Then Set oWB = oXL.Workbooks.Add
  If oWB Is Nothing Then
    MsgBox "Couldn't find/create Excel workbook.", vbCritical + vbOKOnly, "Excel workbook not found"
    Exit Sub
  End If
  ' Get a reference to the active sheet in Excel
  Set oWS = oXL.ActiveSheet
  If oWS Is Nothing Then Set oWS = oWB.Worksheets.Add
  If oWS Is Nothing Then
    MsgBox "Couldn't find/create Excel sheet.", vbCritical + vbOKOnly, "Excel sheet not found"
    Exit Sub
  End If
  On Error GoTo errorhandler
  ' Get a reference to the active presentation
  Set oPres = ActivePresentation
  If oPres Is Nothing Then
    MsgBox "Couldn't find active presentation.", vbCritical + vbOKOnly, "Active presentation not found"
    Exit Sub
  End If
  ' Recurse all shapes in all slides in the active presentation
  For Each oSld In oPres.Slides
    lCol = 1
    For Each oShp In oSld.Shapes
      lRow = oSld.SlideIndex ' maybe add an offset for a header row?
      With oShp
        If oShp.Type = msoTextBox Then ' we could also do a wider search with all shapes and check if they have text
          With .TextFrame
            If .HasText Then
              oWS.Cells(lRow, lCol).Value = .TextRange.Text
              oWS.Cells(lRow, lCol).Value = oShp.Name & " is empty"
            End If
            lCol = lCol + 1
          End With
        End If
      End With
  ' Clean up
  Set oXL = Nothing: Set oWB = Nothing: Set oWS = Nothing
  Set oPres = Nothing: Set oSld = Nothing: Set oShp = Nothing
  Exit Sub
  Debug.Print Err, Err.Description
  Resume Next
End Sub

Open in new window


Does this VBA go into the Powerpoint or into the Excel file? In what sequence will this routine extract the shapes into Excel? In the order the shapes were created? Will it extract the shapes in the same order in each slide?
Jamie GarrochSenior Technical Consultant at BrightCarbon
Answers as follows:

1. It's designed to go in the PowerPoint VBE as stated in line 3. It could go in the Excel VBE with modification.
2. It will recurse through the slide's collection of shapes according to their index which is equivalent to their ZOrder (layer). Press Alt+F10 to open the Selection Pane in PowerPoint and the order starts from 1 at the bottom of the list and increases to the total number of objects on the slide (oSld.Shapes.Count). This may or may not be the same as order as the shapes were created. It will not if their layer has been changed once they were created. Again, it could be modified to look at the shape's Name property or other property as required.

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