Tallying data sheets in a Word document

[Janet McRae]

I have multiple worksheet objects within a Word document that I need to have
a single cell from each tallied for a final number in a summary paragraph. Is
there a way to do that?

 

[Helmut Weber]

Hi Janet,

I don't yet understand all of it,
adapted from the book from
Meister, Gahler, Freßdorf, Jamieson,
so far available only in german:

Sub Test40012()
Dim sTmp As String
Dim oShp As InlineShape
Dim OLEF As OLEFormat
Set OLEF = ActiveDocument.InlineShapes(1).OLEFormat
Application.ScreenUpdating = False
sTmp = (GetFromExcelOLE(OLEF, 2, 3))
MsgBox sTmp
End Sub

Public Function GetFromExcelOLE( _
OLEF As Word.OLEFormat, _
lRow As Long, lClm As Long) As String

Dim xlApp As Excel.Application
Dim xlWrk As Excel.Workbook
Dim xlSht As Excel.Worksheet

OLEF.DoVerb verbindex:=wdOLEVerbHide
Set xlWrk = OLEF.Object
Set xlApp = xlWrk.Application
Set xlSht = xlWrk.ActiveSheet

GetFromExcelOLE = xlSht.Cells(lRow, lClm).Value
xlApp.quit
Set xlApp = Nothing
End Function

To loop over the inlineshapes in question, is up to you,
also whether the objects are inlineshapes or shapes,
and whether you deal with numbers or strings.

To the best of my knowledge.
It's especially the doverb-method,
that I'm not confident with.

I got a feeling, there are lots
of improvements possible.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"