Recomputed worksheets embedded in a document

[Josh Sale]

First let me apologize for my ignorance. I've been working with the Excel
object model for many years and know it well. But I know next to nothing
about Word's object model. So be kind.

Basically what I'm hoping to do is create a Document_Open macro that walks
the document's structure looking for embedded (but not linked) Excel
worksheet objects. For each Excel worksheet object it finds, I want this
macro to ask Excel to recompute the contents of the worksheet.

Is this possible? If so, can somebody provide a little guidance?

TIA,

josh

p.s. It would be nice if the solution was pretty release independent, but I
could live with a solution that only worked for Word 2003 and 2007.

 

[Jean-Guy Marcil]

Josh Sale was telling us:
Josh Sale nous racontait que :

First let me apologize for my ignorance. I've been working with the
Excel object model for many years and know it well. But I know next
to nothing about Word's object model. So be kind.

Basically what I'm hoping to do is create a Document_Open macro that
walks the document's structure looking for embedded (but not linked)
Excel worksheet objects. For each Excel worksheet object it finds, I
want this macro to ask Excel to recompute the contents of the
worksheet.
Is this possible? If so, can somebody provide a little guidance?

TIA,

josh

p.s. It would be nice if the solution was pretty release
independent, but I could live with a solution that only worked for
Word 2003 and 2007.

Why?
If the worksheet are embedded, then the only way to modify their content is
by double clicking them. As soon as you do that, you are in the "Excel" mode
and what ever changes you make, Excel computes right away.

So it seems unnecessary to write a macro to do as you want since it will not
have any effect.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Josh Sale]

The clicking of the embedded worksheets is exactly what I'm trying to avoid.
The users don't want to have to click the worksheets ... but they want to
see the most current values.

Can this be done by a macro?

josh

 

[Jean-Guy Marcil]

Josh Sale was telling us:
Josh Sale nous racontait que :

The clicking of the embedded worksheets is exactly what I'm trying to
avoid. The users don't want to have to click the worksheets ... but
they want to see the most current values.

This is exactly what I was trying to write. It is not necessary to have a
macro to "update" the values in an embedded worksheet because the values are
always up to date, unless the embedded worksheet contains information that
is linked to a third source...

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Josh Sale]

Just so. The worksheet gets its data from user defined functions that
access a database. So the point of the refresh is to cause those functions
to get the current values from the database so they can be displayed in the
Word document.

Sorry I wasn't clearer about this.

So can it be done?

j

 

[Jean-Guy Marcil]

Josh Sale was telling us:
Josh Sale nous racontait que :

Just so. The worksheet gets its data from user defined functions that
access a database. So the point of the refresh is to cause those
functions to get the current values from the database so they can be
displayed in the Word document.

Sorry I wasn't clearer about this.

So can it be done?

Try something like this (This assumes that your embedded Excel Sheets are
inline with the text):

'_______________________________________
Option Explicit
'_______________________________________
Sub UpdateInlinshapeExcel()

Dim Shp As InlineShape
Dim Doc As Document
Dim objEXL As Object
Dim rgeStart As Range

Set Doc = ActiveDocument
Set rgeStart= Selection.Range

For Each Shp In Doc.InlineShapes
If Shp.Type = wdInlineShapeEmbeddedOLEObject Then
If Shp.OLEFormat.ProgID = "Excel.Sheet.8" Then
Shp.OLEFormat.Activate
Set objEXL = Shp.OLEFormat.Object
With objEXL.ActiveSheet
.Calculate
End With
SendKeys "{ESC}"
End If
End If
Next Shp

rgeStart.Select

End Sub
'_______________________________________


--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Josh Sale]

Jean-Guy,

Thank you (or should I say Merci?)!

I tried your supplied code and it almost works perfectly.

First, Activating the inline shape did the trick. The next few lines of
code that gets a reference to the Excel worksheet and then forces the
recalculation proves to be unnecessary in my situation. When the code does
the Activate, the embedded spreadsheet recalculates itself and all of my
UDF's run and I get fresh data.

Second, when the macro completes, the embedded worksheet is still selected.
It still has the cross-hatched border all around it, Excel's row & column
headers are still displayed as is the worksheet tab name at the bottom.
After the macro completes, I can click into another part of the document and
my embedded worksheet is deselected and returns to its normal appearance.

I presume you tried to handle this case with the rgeStart.Select at the
bottom of the loop. But it doesn't seem to quite do the trick. I tried to
turn on the macro recorder and just select something with the mouse, but it
appears that Word's macro recorder disables the mouse (and when I think
about it for a few seconds I can imagine why that is the case).

Can you think of any other little trick for moving the focus away from the
embedded worksheet?

Thanks again for your help on this.

josh

 

[Jean-Guy Marcil]

Josh Sale was telling us:
Josh Sale nous racontait que :

Jean-Guy,

Thank you (or should I say Merci?)!

I tried your supplied code and it almost works perfectly.

First, Activating the inline shape did the trick. The next few lines
of code that gets a reference to the Excel worksheet and then forces
the recalculation proves to be unnecessary in my situation. When the
code does the Activate, the embedded spreadsheet recalculates itself
and all of my UDF's run and I get fresh data.

But now you know how to modify the content of the Excel embedded object!

Second, when the macro completes, the embedded worksheet is still
selected. It still has the cross-hatched border all around it,
Excel's row & column headers are still displayed as is the worksheet
tab name at the bottom. After the macro completes, I can click into
another part of the document and my embedded worksheet is deselected
and returns to its normal appearance.
I presume you tried to handle this case with the rgeStart.Select at
the bottom of the loop. But it doesn't seem to quite do the trick. I
tried to turn on the macro recorder and just select something with
the mouse, but it appears that Word's macro recorder disables the
mouse (and when I think about it for a few seconds I can imagine why
that is the case).
Can you think of any other little trick for moving the focus away
from the embedded worksheet?

Thanks again for your help on this.

On machine, the
SendKeys "{ESC}"
statement does just this (but not when debugging or from the VBA Editor
window, you have to call the macro from ALT-F8 or with a toolbar button).

I know SendKeys is "barbaric", but I do not know of any other way to do
this.

The
rgeStart.Select
statement is there just in case the SendKeys changes the user selection.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

Josh Sale was telling us:
Josh Sale nous racontait que :

Jean-Guy,

Thank you (or should I say Merci?)!

I tried your supplied code and it almost works perfectly.

First, Activating the inline shape did the trick. The next few lines
of code that gets a reference to the Excel worksheet and then forces
the recalculation proves to be unnecessary in my situation. When the
code does the Activate, the embedded spreadsheet recalculates itself
and all of my UDF's run and I get fresh data.

But now you know how to modify the content of the Excel embedded object!

Second, when the macro completes, the embedded worksheet is still
selected. It still has the cross-hatched border all around it,
Excel's row & column headers are still displayed as is the worksheet
tab name at the bottom. After the macro completes, I can click into
another part of the document and my embedded worksheet is deselected
and returns to its normal appearance.
I presume you tried to handle this case with the rgeStart.Select at
the bottom of the loop. But it doesn't seem to quite do the trick. I
tried to turn on the macro recorder and just select something with
the mouse, but it appears that Word's macro recorder disables the
mouse (and when I think about it for a few seconds I can imagine why
that is the case).
Can you think of any other little trick for moving the focus away
from the embedded worksheet?

Thanks again for your help on this.

On machine, the
SendKeys "{ESC}"
statement does just this (but not when debugging or from the VBA Editor
window, you have to call the macro from ALT-F8 or with a toolbar button).

I know SendKeys is "barbaric", but I do not know of any other way to do
this.

The
rgeStart.Select
statement is there just in case the SendKeys changes the user selection.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Josh Sale]

You were exactly right again!

Thanks again!

josh