Extract Powerpoint Textboxes into Excel

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?
Member_2_7966563Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
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?
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
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
#Else
  ' 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
            Else
              oWS.Cells(lRow, lCol).Value = oShp.Name & " is empty"
            End If
            lCol = lCol + 1
          End With
        End If
      End With
    Next
  Next
  
  ' Clean up
  Set oXL = Nothing: Set oWB = Nothing: Set oWS = Nothing
  Set oPres = Nothing: Set oSld = Nothing: Set oShp = Nothing
  
  Exit Sub
  
errorhandler:
  Debug.Print Err, Err.Description
  Resume Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_7966563Author Commented:
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?
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft PowerPoint

From novice to tech pro — start learning today.