Retrieve data from an Excel
worksheet embedded in a PPT
slide -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 IDEDim oWorkbook
As Excel.WorkbookDim oWorksheet As Excel.WorksheetDim oSh As
ShapeDim LastCol As LongDim LastRow As LongDim x As LongDim
y As Long'' This example assumes you''ve selected the excel object''
you want to work withSet oSh =
ActiveWindow.Selection.ShapeRange(1)Set oWorkbook =
oSh.OLEFormat.Object'' Use the first sheet in the work bookSet oWorksheet
= oWorkbook.worksheets(1)'' Get the last row/colWith
oWorksheet.Activate'' Find the extents of the data in the
sheetLastRow = .Range("a65535").End(xlUp).RowLastCol =
.Range("iv1").End(xlToLeft).Column'' Display the dataFor x = 1 To
LastRowFor y = 1 To LastColDebug.Print "Row" & CStr(x) & ":Col"
& CStr(y) & " " & .Cells(x, y)NextNextEnd
WithoWorkbook.Close (False)Set oWorkbook = NothingSet oWorksheet
= NothingEnd Sub