Referencing Excel Sheet placed in PowerPoint

Question

I have added an Excel sheet into a PowerPoint 2010 document using the Insert tab --> Object --> Microsoft Excel 97-2003 Worksheet (Create New) option. I want to reference some of the cells in the Excel sheet in another slide of my PowerPoint. Is there a way to do this?

The purpose is I have a client who insists on a PPT report, except I need to use Excel to create the information required. Rather than constantly having two documents open and transferring the info from the Excel sheet to the PPT slide, I wanted to consolidate into one document, thus the Excel sheet added into the PPT file.

I'm not an expert at VBA by any means, but I know enough to muddle my way through if I need to use VBA to accomplish this.

I've uploaded pictures of an example (I hope). On slide 1, I have three cells filled in using the inserted Excel sheet. Slide 2 is where I need to reference those cells (text boxes with text in red). The information in those cells will change week from week and I need the text boxes in slide 2 to update with it. Any help would be appreciated.

How is the data getting in to the embedded XLS sheet? If that's coming from some other Excel file (presumably) it's probably easiest just to access that file and pull the required information from it, rather than manually inserting it in the embedded sheet and then retrieving the data from embedded sheet. Seems redundant. — David Zemens

David Zemens David Zemens · Accepted Answer · 2017-06-03T05:28:52

Use the Selection Pane in PowerPoint to identify the embedded object's name, and reference it, then use the OLEFormat.Object to get a handle on the Workbook object, and from there you're just working with an instance of Excel.Workbook class, so all your familiar Excel properties & methods should be available:

Option Explicit
'Requires reference to Excel
Sub populate_text_box()
Dim obj As Object
Dim tb As Shape 'TextBox
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set obj = ActivePresentation.Slides(1).Shapes("Object 3")


Set wb = obj.OLEFormat.Object
Set ws = wb.Sheets(1)

Set tb = ActivePresentation.Slides(2).Shapes("TextBox 1") 'Modify slide/shape name as needed
tb.TextFrame2.TextRange.Text = ws.Range("B2").Value  

Set tb = ActivePresentation.Slides(2).SHapes("TextBox 2") 'Modify slide/shape name as needed
tb.TextFrame2.TextRange.Text = ws.Range("D2").Value

Set tb = ActivePresentation.Slides(2).SHapes("TextBox 3") 'Modify slide/shape name as needed
tb.TextFrame2.TextRange.Text = ws.Range("F2").Value

End Sub

Referencing Excel Sheet placed in PowerPoint

1 Answers