Automate Excel from PowerPoint. Automate PowerPoint from Excel. And so on.
Excel MVP Jon Peltier's site includes this very useful Using Excel with Other Office Applications section. Recommended reading!
And here's a simple example of how you can retrieve data from an Excel worksheet embedded in a PPT slide.
It assumes you've copy/pasted a selection from an Excel worksheet into your presentation or used Insert, Object to bring in an Excel worksheet.
Be sure to select the Excel object before running the code. There's no error handling to protect you here.
Also, be sure the Immediate window is open; that's where the results are displayed. Press Ctrl+G to open it.
Sub GetXLSWorksheetDataExample()
' This assumes you've set a reference to the MS Excel object library in the IDE
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim oSh As Shape
Dim LastCol As Long
Dim LastRow As Long
Dim x As Long
Dim y As Long
' This example assumes you've selected the excel object
' you want to work with
Set oSh = ActiveWindow.Selection.ShapeRange(1)
Set oWorkbook = oSh.OLEFormat.Object
' Use the first sheet in the work book
Set oWorksheet = oWorkbook.worksheets(1)
' Get the last row/col
With oWorksheet
.Activate
' Find the extents of the data in the sheet
LastRow = .Range("a65535").End(xlUp).Row
LastCol = .Range("iv1").End(xlToLeft).Column
' Display the data
For x = 1 To LastRow
For y = 1 To LastCol
Debug.Print "Row" & CStr(x) & ":Col" & CStr(y) & " " & .Cells(x, y)
Next
Next
End With
oWorkbook.Close (False)
Set oWorkbook = Nothing
Set oWorksheet = Nothing
End Sub