通过VBA在Word文档中修改嵌入式Excel工作簿 [英] Modify embedded Excel workbook in Word document via VBA

问题描述

我有一个Word文档，其中包含两个嵌入式Excel文件（使用插入 - >对象 - >从文件创建），我想使用Word VBA进行修改。我已经到了这一点，我可以打开嵌入式文件进行编辑（见下面的代码），但是无法获取Excel工作簿上的句柄，我可以使用它来修改并保存嵌入文件。有人有这个解决方案吗？感谢提前。

  Sub TestMacro（）
 
 Dim lNumShapes As Long 
 Dim lShapeCnt As Long 
 Dim xlApp As Object 
 Dim wrdActDoc As Document 
 
设置wrdActDoc = ActiveDocument 
 
对于lShapeCnt = 1到1'wrdActDoc.InlineShapes.Count 
如果wrdActDoc.InlineShapes（lShapeCnt）.Type = wdInlineShapeEmbeddedOLEObject然后
如果wrdActDoc.InlineShapes（lShapeCnt）.OLEFormat.ProgID =Excel.Sheet.8然后
'这将打开嵌入式Excel工作簿使用Excel 
 wrdActDoc.InlineShapes（lShapeCnt）.OLEFormat.Edit 
如果
结束If If 
 Next lShapeCnt 
 
 End Sub 
  

解决方案

Yikes，不要在你的评论中做你建议的内容。您可能会遇到多个Excel实例（检查任务管理器，并查看执行代码后有多少）。

首先，添加对Excel的引用对象库（Project-> References&选择Microsoft Excel对象库）。现在，您可以将对象声明为真正的Excel类型，并使用早期绑定，而不是将其声明为对象并使用后期绑定。这不是绝对必要的，但除了其他任何东西，它意味着您在编辑代码时获得Intellisense。

您正在做正确的事情直到你做。 OleFormat.Edit。 （我会亲自使用.OleFormat.Activate，但是因为我从来没有尝试使用.Edit我不能说它有所作为）。

完成了。 （或者，大概是.Edit），你可以访问OleFormat.Object成员。由于嵌入的对象是Excel图表，对象将是Excel工作簿，因此您可以执行以下操作：

 
 Dim oOleFormat as OleFormat 
设置oOleFormat = ... 
 
 oOleFormat.Activate 
 
 Dim oWorkbook作为Excel.Workbook 
设置oWorkbook = oOleFormat.Object 
 
'与工作簿一起做东西
 oWorkbook.Charts（1）.ChartArea.Font.Bold = True 
 

请注意，您不需要关闭Excel，实际上您不能 - Word拥有用于编辑的实例，并将决定何时关闭它。这实际上是一个问题，因为没有明显的方法强制嵌入对象被禁用，所以在执行上面的代码之后，图表将保持打开状态。

虽然有一个黑客方式让图表关闭，如果你添加告诉Word激活它作为别的东西，它会首先停用它。所以，如果你把它激活为非感性的东西，你会得到正确的结果，因为它会去激活它，然后不能重新激活它。所以，添加以下行：

 
 oOleFormat.ActivateAsThis.Class.Does.Not.Exist
 

请注意，这将引发错误，因此您需要使用错误恢复下一步临时禁用错误处理。因此，我通常会创建一个Deactivate方法，以避免中断我的main方法中的错误处理。如下：

私有子DeactivateOleObject（ByRef oOleFormat as OleFormat）
错误恢复下一个
oOleFormat.ActivateAs This.Class.Does.Not.Exist
End Sub

希望这有帮助。
加里

I have a Word document with two embedded Excel files (added using Insert -> Object -> Create From File) which I wish to modify using Word VBA. I have got to the point where I am able to open the embedded files for editing (see code below), but am unable to get a handle on the Excel workbook using which I can make the modifications and save the embedded file. Does anyone have a solution for this? Thanks in advance.

Sub TestMacro()

    Dim lNumShapes As Long
    Dim lShapeCnt As Long
    Dim xlApp As Object
    Dim wrdActDoc As Document

    Set wrdActDoc = ActiveDocument

    For lShapeCnt = 1 To 1 'wrdActDoc.InlineShapes.Count
        If wrdActDoc.InlineShapes(lShapeCnt).Type = wdInlineShapeEmbeddedOLEObject Then
            If wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.ProgID = "Excel.Sheet.8" Then
                'This opens the embedded Excel workbook using Excel
                wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.Edit
            End If
        End If
    Next lShapeCnt

End Sub

解决方案

Yikes, don't do what you're suggesting in your comment. You'll probably end up with multiple instances of Excel (check Task Manager and see how many there are after executing your code).

Firstly, add a reference to the Excel object library (Project->References & choose Microsoft Excel Object Library). Now you can declare your objects as bona-fide Excel types and use early binding rather than declaring them as "Object" and using late binding. This isn't strictly necessary, but apart from anything else it means you get Intellisense when editing your code.

You're doing the right thing right up until you do .OleFormat.Edit. (I would personally use .OleFormat.Activate but since I've never tried using .Edit I couldn't say that it makes a difference).

Having done .Activate (or, presumably, .Edit), you can then access the OleFormat.Object member. Since the embedded Object is an Excel chart, the "Object" will be the Excel Workbook, so you can do this:

Dim oOleFormat as OleFormat
Set oOleFormat = ...

oOleFormat.Activate

Dim oWorkbook As Excel.Workbook
Set oWorkbook = oOleFormat.Object

' Do stuff with the workbook
oWorkbook.Charts(1).ChartArea.Font.Bold = True

Note that you do NOT need to close Excel, and indeed you cannot - Word "owns" the instance used for an edit-in-place, and will decide when to close it. This is actually something of a problem, since there's no obvious way to force the embedded object to be de-activated, so the chart would stay open after you execute the code above.

There is a hack-y way to get the chart to close, though. If you add tell Word to activate it as something else, it'll de-activate it first. So, if you tell it to activate it as something non-sensical, you'll achieve the right result because it'll de-activate it and then fail to re-activate it. So, add the following line:

oOleFormat.ActivateAs "This.Class.Does.Not.Exist"

Note that this will raise an error, so you'll need to temporarily disable error handling using On Error Resume Next. For that reason, I normally create a Deactivate method, to avoid disrupting the error handling in my main method. As in:

Private Sub DeactivateOleObject(ByRef oOleFormat as OleFormat)
    On Error Resume Next
    oOleFormat.ActivateAs "This.Class.Does.Not.Exist"
End Sub

Hope this helps. Gary

这篇关于通过VBA在Word文档中修改嵌入式Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！