通过 VBA 修改 Word 文档中嵌入的 Excel 工作簿

Modify embedded Excel workbook in Word document via VBA

提问by Anindya

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.

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

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

采纳答案by Gary McGill

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).

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

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.

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

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).

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

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:

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

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.

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

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:

不过，有一种 hack-y 方法可以关闭图表。如果您添加告诉 Word 将其激活为其他内容，它将首先停用它。因此，如果您告诉它将其激活为无意义的东西，您将获得正确的结果，因为它会停用它，然后无法重新激活它。因此，添加以下行：

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:

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

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

Hope this helps. Gary

希望这可以帮助。加里

回答by Perry

Have another hackey way to get the chart to close: Simply use the find function to find something in the document that is not there.

有另一种让图表关闭的黑客方法：只需使用 find 函数查找文档中不存在的内容。

EG

例如

With Selection.Find
    .ClearFormatting
    .Text = "wiffleball"
    .Execute Forward:=True
End With

This will take you out of the embedded file, close the instance and back to the main document, you can just code from there.

这将使您退出嵌入文件，关闭实例并返回主文档，您可以从那里进行编码。

Hope this helps, this problem was driving me crazy.

希望这会有所帮助，这个问题让我发疯。

回答by Anindya

I have a solution to my own problem. Any further comments will be appreciated -

我有自己的问题的解决方案。任何进一步的评论将不胜感激 -

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
                wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.Edit
                Set xlApp = GetObject(, "Excel.Application")
                xlApp.Workbooks(1).Worksheets(1).Range("A1") = "This is A modified"
                xlApp.Workbooks(1).Save
                xlApp.Workbooks(1).Close
                xlApp.Quit
            End If
        End If
    Next lShapeCnt

End Sub