-1

I have many Excel objects are there embedded in a MS-Word Document.

I want to calculating the grand total: with summing the totals are there in the each specified excel object and return that grand total in the MS-Word document.

Macro holder is MS-Word Document's VBA module.

Means: I need to access to an specified embedded Excel object, form the MS-Word module, then perform it active, then assign to an object-variable by -For example:- ExcelApplication = GetObject(, "Excel.Application") statement. Then try to access its appropriated total values , by -For example:- Total = Range("Table1[[#Totals],[Amount]]").Value. Point is all tables Name are in the Excel objects is Table1 which contains the Amount Columns and the Total Row.

Note is in above Excel objects, The first row which contains the Table Header is Hided.

Example

enter image description here

Sample File

This document have extending daily.

I need a macro in the Normal.dotm Which calculating the grand total of all specified Excel object (specified with assigning a name to them or ...) and perform returning this value with Selection.TypeText Text:= where is selected in picture below: (at the end of document)

Why I insist to have embedded Excel object?

  1. Because I have formula for calculating Column1: A, B, C, ....
  2. Because I have a hided Data base Sheet for data validation Items
  3. I have Formula in Amount column for multiplying the rates and the amount of each item-unit which is in Data base sheet
Community
  • 1
  • 1
Tuberose
  • 434
  • 6
  • 24
  • Can you sum if before embedding? Also, please provide a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) – QHarr Jan 08 '18 at 08:24
  • The embedded objects are separated and I use embedding Excel documents approach for reliability and simplify in calculating totals of each part. Point is this report can not be produce in Excel, entirely. – Tuberose Jan 08 '18 at 09:17
  • Why are you embedding Excel objects for what appears to be just as possible with Word tables - the summation of which can be done far more easily? – macropod Jan 09 '18 at 00:01

2 Answers2

2

In that case, try something along the lines of:

Sub TallyXLVals()
Application.ScreenUpdating = False
Dim Rng As Range, objOLE As Word.OLEFormat, objXL As Object
Dim i As Long, lRow As Long, sValA As Single, sValB As Single, sValC As Single
Const xlCellTypeLastCell As Long = 11
With ActiveDocument
  .ActiveWindow.Visible = False
  For i = .InlineShapes.Count To 1 Step -1
    With .InlineShapes(i)
      If Not .OLEFormat Is Nothing Then
        If Split(.OLEFormat.ClassType, ".")(0) = "Excel" Then
          Set Rng = .Range
          Set objOLE = .OLEFormat
          objOLE.Activate
          Set objXL = objOLE.Object
          With objXL.ActiveSheet
            lRow = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
            sValA = sValA + .Range("A" & lRow).Value
            sValB = sValB + .Range("B" & lRow).Value
            sValC = sValC + .Range("C" & lRow).Value
          End With
          objXL.Application.Undo
        End If
      End If
    End With
  Next
  Call UpdateBookmark("BkMkA", Format(sValA, "$#,##0.00"))
  Call UpdateBookmark("BkMkB", Format(sValB, "$#,##0.00"))
  Call UpdateBookmark("BkMkC", Format(sValC, "$#,##0.00"))
  .ActiveWindow.Visible = True
End With
Set objXL = Nothing: Set objOLE = Nothing: Set Rng = Nothing
Application.ScreenUpdating = True
End Sub

Sub UpdateBookmark(StrBkMk As String, StrTxt As String)
Dim BkMkRng As Range
With ActiveDocument
  If .Bookmarks.Exists(StrBkMk) Then
    Set BkMkRng = .Bookmarks(StrBkMk).Range
    BkMkRng.Text = StrTxt
    .Bookmarks.Add StrBkMk, BkMkRng
  End If
End With
Set BkMkRng = Nothing
End Sub

where the locations you want the outputs to appear are bookmarked, with the names BkMkA, BkMkB, & BkMkC, respectively.

Note: Because you're activating embedded objects, there is unavoidable screen flicker.

macropod
  • 12,757
  • 2
  • 9
  • 21
0

Your own effort is insufficent. Here is code to start you off. The code will loop through all the InlineShapes in your Word document, select the first one which represents an Excel worksheet and opens that item for editing. It is the same action which you can recreate in the document by right-clicking on the embedded Excel table, selecting "Worksheet Object" and "Edit".

Private Sub OpenEmbeddedExcelInWord()
    ' 08 Jan 2018

    Dim Shp As InlineShape

    For Each Shp In ActiveDocument.InlineShapes
        With Shp
            If Shp.Type = wdInlineShapeEmbeddedOLEObject Then Exit For
        End With
    Next Shp

    Shp.OLEFormat.Edit
End Sub
Variatus
  • 14,293
  • 2
  • 14
  • 30
  • I want to calculating the grand total with summing the totals are there in the all specified excel objects and return that in the MS-Word document. (I need access an specified embedded excel object, form the MS-Word model. then try to access appropriated values by `ExcelApplication = GetObject(, "Excel.Application")`, and Total = Range("Table1[[#Totals],[Amount]]")`. Point is the all table names are in Excel objects are `Table1` and all objects a table with `Amound` columns name. – Tuberose Jan 08 '18 at 11:35
  • The `wdInlineShapeEmbeddedOLEObject`is represents every embedded objects are in the MS-Word document. Indeed how can I check the the embedded object which founds is an Excel one? – Tuberose Jan 08 '18 at 17:50
  • Precisely. This is what I meant when I wrote that your own effort is insufficient. The question you now ask is different from your original one and answering it will serve no other purpose but to lead to the next. Your own effort must bridge the gap between what you have and what you want or, in other words, your question should demand an answer that actually helps you. – Variatus Jan 09 '18 at 00:36
  • In my above code `Debug.Print Shp.ProgID` will print the name of the program used by the embedded object. You can check that name before opening `Shp` for editing. – Variatus Jan 09 '18 at 00:38