How to extract an embedded worksheet from Powerpoint and save it using VB?

I have a bunch of Powerpoint presentations with embedded excel worksheets that I would like to throw into one Excel workbook.  I am trying to have Excel open each presentation and grab the embedded worksheet out of Powerpoint and save it into a single directory and then have all of the workbooks condensed into a single workbook using VB.  Any help I can get on this is greatly appreciated.

Thank you in advance!
Mike
mander6618Asked:
Who is Participating?
 
RobOwner (Aidellio)Commented:
this will loop through all slides in a presentation looking for an excel workbook / spreadsheet
Sub GetWorkSheetName()
    ' ignore errors when shape is not an Excel Workbook
    On Error Resume Next
    
    ' need to include in references "Microsoft Excel [version] Object Library"
    Dim xl As Excel.Workbook
    
    ' all shapes on a slide as the embedded excel workbook is a "shape"
    Dim current_slide_shapes As Shapes
    For j = 1 To ActivePresentation.Slides.Count
        ' get all the shapes on the current slide
        Set shp = ActivePresentation.Slides(j).Shapes
        ' loop through all the shapes looking for an excel workbook
        For i = 1 To shp.Count
            Set xl = shp(i).OLEFormat.Object
            If (xl Is Not Null) Then
                ' found a workbook
                ' xl now holds the reference to the embedded workbook
                Debug.Print xl.Name
            End If
        Next i
    Next j
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.