linking a powerpoint shape to an embedded excel object

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All,

I have a powerpoint with an embedded Excel chart & worksheet.

The same slide also has a few Powerpoint Text objects. What I'd like to do,
if possible, is this:

When the user edits the Excel worksheet (e.g., changes some values), the
Powerpoint text objects should update to display the values of certain cells.

In other words, I'd like to link the Text in the Powerpoint Text objects to
certain cells in the embedded Excel Worksheet, so that changes in one (the
worksheet) are reflected in the other.

Is this possible?

Many thanks in advance!

Cheers,
Matt Stuehler
 
Mstuehler said:
All,

I have a powerpoint with an embedded Excel chart & worksheet.

The same slide also has a few Powerpoint Text objects. What I'd like to do,
if possible, is this:

When the user edits the Excel worksheet (e.g., changes some values), the
Powerpoint text objects should update to display the values of certain cells.

In other words, I'd like to link the Text in the Powerpoint Text objects to
certain cells in the embedded Excel Worksheet, so that changes in one (the
worksheet) are reflected in the other.
Click to expand...

I don't think so, at last not w/o some fairly complex VBA code behind it.

PowerPoint would only be able to update data when it's linked to an external
file. Embedded objects have no "knowledge" of one another's existence and
there's no file to communicate with.

Can you use links to Excel files instead?
 
Shyam,

Thank you for this excellent response - it looks like it's exactly what I'm
trying to do.

Unfortunately, I don't fully understand how to implement this -
specifically, where do I add this code.

My experience is limited to adding vba code in a code module within
Powerpoint (accessed through Alt-F11). But it looks like this code should be
added directly to the Excel Object?

Can you provide a bit more explanation.

Of course, no worries if you're too busy.

Thanks again!

Cheers,
Matt Stuehler
 
Matt,
Proof of concept.

1. Create an Excel Workbook.
2. Launch the Visual Basic Editor (Alt+F11)
3. In the project explorer double-click the WorkBook object to bring up the
code window.
4. Paste the following code into it.
' ==============================
Private Sub Workbook_Deactivate()
Dim oPPT As Object
Set oPPT = GetObject(, "PowerPoint.Application")
If Not oPPT Is Nothing Then
With oPPT
' Assumes that the activepresentation is the one which contains this
excel object)
' Paste the contents of cell A1 into the 1st shape on the 1st slide.
.ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.Text =
Worksheets(1).Range("A1")
End With
End If
End Sub
'===============================
4a. Optionally digitally sign the VBA project with a self certificate to
avoid macro warnings.
5. Close VBE.
6. Save the workbook.
7. Create new presentation.
8. Insert | object from file and select the excel workbook created above.
9. Enable the macro when prompted.
10. You will immediately see that the 1st shape on slide 1 updates to the
current value in cell A1 of the spreadsheet,

Now, when you double-click on the excel object and edit cell A1 it will show
the updated value in PPT when you close the excel session.
Let me know if you want me to send you a sample file incase you have
problems with the steps provided above.

--
Regards
Shyam Pillai

Secure Pack: http://skp.mvps.org/securepack/
 
Dear Shyam,

I have been working on a Powerpoint project for several weeks now. During
that time, I've posted five separate questions to this forum. Each time,
you've provided an excellent solution, even in cases where other people told
me the problem could not be solved.

Many, many thanks for all of this help and expertise. I could not have
completed this project were it not for your guidance.

Best regards,
Matt Stuehler
 
Back
Top