引用嵌入在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 isTable1
which contains theAmount
Columns and theTotal 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列的公式:A,B,C,....
- 因为我有一个隐藏的数据库表用于数据验证项目
- 我在金额"列中有一个公式,用于将费率和 数据库表中每个项目单位的数量
- Because I have formula for calculating Column1: A, B, C, ....
- Because I have a hided Data base Sheet for data validation Items
- 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屋!