引用嵌入在MS-Word文档中的Excel对象? [英] Referring Excel objects which embedded in a MS-Word Document?

问题描述

我有很多Excel对象嵌入在MS-Word文档中.

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

我想用求和计算总计:，每个指定的excel对象中都存在总计，并返回 MS-Word文档.

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.

宏持有者是MS-Word Document的VBA模块.

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

方法: :我需要访问到指定的嵌入式 Excel对象，形成 MS -Word模块，然后对其执行活动，然后将分配给对象变量，例如-: ExcelApplication = GetObject(, "Excel.Application") 陈述.然后尝试通过-例如: Total = Range("Table1[[#Totals],[Amount]]").Value 访问其相应的总值.点是所有表Excel对象中的名称是Table1，其中包含Amount列和Total Row.

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.

注意在上面的Excel对象中，包含表标题的第一行被隐藏.

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

示例文件

此文档每天都会扩展.

This document have extending daily.

我需要Normal.dotm中的一个宏，该宏计算所有指定的Excel对象(通过为其指定名称或...来指定)的总计，并使用在下面的图片中选择的位置:(在文档末尾)

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)

---

为什么我坚持要嵌入Excel对象?

1. 因为我有计算第1列的公式:A，B，C，....
2. 因为我有一个隐藏的数据库表用于数据验证项目
3. 我在金额"列中有一个公式，用于将费率和 数据库表中每个项目单位的数量

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

推荐答案

在这种情况下，请尝试以下方法:

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

要在其中显示输出的位置被标记为书签，名称为BkMkA，BkMkB和& BkMkC.

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.

这篇关于引用嵌入在MS-Word文档中的Excel对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！