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

问题描述

我有一个 Word 文档，其中包含两个嵌入的 Excel 文件(使用插入 -> 对象 -> 从文件创建)，我希望使用 Word VBA 对其进行修改.我已经到了可以打开嵌入文件进行编辑的地步(请参阅下面的代码)，但无法处理 Excel 工作簿，我可以使用它进行修改并保存嵌入文件.有没有人对此有解决方案?提前致谢.

Sub TestMacro()Dim lNumShapes As LongDim lShapeCnt 只要长Dim xlApp 作为对象Dim wrdActDoc 作为文档设置 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万一万一下一个 lShapeCnt结束子

解决方案

哎呀，不要按照你在评论中的建议去做.您可能最终会得到多个 Excel 实例(检查任务管理器并查看执行代码后有多少个实例).

首先，添加对 Excel 对象库的引用(Project->References & 选择 Microsoft Excel Object Library).现在，您可以将对象声明为真正的 Excel 类型并使用早期绑定，而不是将它们声明为对象"并使用后期绑定.这不是绝对必要的，但除此之外，这意味着您在编辑代码时会获得智能感知.

在执行 .OleFormat.Edit 之前，您一直在做正确的事情.(我个人会使用 .OleFormat.Activate 但由于我从未尝试过使用 .Edit 我不能说它有什么不同.

完成 .Activate(或者，大概是 .Edit)后，您可以访问 OleFormat.Object 成员.由于嵌入的对象是 Excel 图表，对象"将是 Excel 工作簿，因此您可以这样做:

<前>将 oOleFormat 调暗为 OleFormat设置 oOleFormat = ...oOleFormat.ActivateDim oWorkbook As Excel.Workbook设置 oWorkbook = oOleFormat.Object' 用工作簿做事oWorkbook.Charts(1).ChartArea.Font.Bold = True

请注意，您不需要关闭 Excel，实际上也不能 - Word拥有"用于就地编辑的实例，并将决定何时关闭它.这实际上是一个问题，因为没有明显的方法可以强制取消激活嵌入的对象，因此在您执行上述代码后图表将保持打开状态.

不过，有一种方法可以让图表关闭.如果您添加告诉 Word 将其激活为其他内容，它会先将其取消激活.因此，如果您告诉它以无意义的方式激活它，您将获得正确的结果，因为它会停用它，然后无法重新激活它.因此，添加以下行:

<前>oOleFormat.ActivateAs "This.Class.Does.Not.Exist"

请注意，这会引发错误，因此您需要使用 On Error Resume Next 暂时禁用错误处理.出于这个原因，我通常会创建一个 Deactivate 方法，以避免中断我的主要方法中的错误处理.如:

<前>Private Sub DeactivateOleObject(ByRef oOleFormat as OleFormat)出错时继续下一步oOleFormat.ActivateAs "This.Class.Does.Not.Exist"结束子

希望这会有所帮助.加里

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屋！