公开 Excel InlineShapes 进行编辑 [英] Expose Excel InlineShapes to edit

问题描述

我正在尝试从 Excel 工作簿 VBA 模块自动填写第三方表单.遗憾的是，他们使用了一个嵌入了内嵌 Excel 工作簿的 Word 文档，其中包含作为我想要编辑的命名范围的单元格.

I am trying to automatize filling in a third party form from an Excel Workbook VBA module. They sadly have gone with a Word document with an in-line Excel workbook embedded, which contains cells as named ranges that I want to edit.

如何将 InlineShapes 对象分配给 Excel.workbook 对象，以便将其公开给 Excel 方法和属性?

How do I assign the InlineShapes object to an Excel.workbook object so that I can expose it to Excel methods and properties?

这是我迄今为止尝试过的:

This is what I have tried so far:

Sub test()
        Dim wdApp As Word.Application
        Set wdApp = CreateObject("word.application")
        wdApp.Visible = true ' for testing
        Dim wdAppendixB As Word.Document
        Set wdAppendixB = wdApp.Documents.Add(ThisWorkbook.Path & "\Templates\form_template.dotx")
        Dim wbAppB As Excel.Workbook
        wdAppendixB.InlineShapes.Item(1).OLEFormat.Edit
        Set wbAppB = wdAppendixB.InlineShapes.Item(1).OLEFormat.Object
        wbAppB.Sheets("Sheet1").Range("date1").Value = "2019-06-02"
Exit Sub

只要脚本打开 OLE 对象进行编辑，脚本就会停止，没有错误.关闭 OLE 对象进行编辑不会恢复脚本.

As soon as the script opens the OLE object for editing, the script stops with no errors. Closing the OLE object for editing does not resume the script.

如果我省略编辑对象，而只是将工作簿对象设置为 OLEFormat.Object，则会出现运行时错误430"类不支持自动化或不支持预期接口".

If I omit editing the object and just set the workbook object to the OLEFormat.Object it errors out with Run-time error '430' "Class does not support Automation or does not support expected interface".

感谢任何建议.

推荐答案

使用 Activate，而不是 Edit(或带有适当的 DoVerbwdOleVerb 常量).

Use Activate, instead of Edit (or DoVerb with an appropriate wdOleVerb constant).

请注意，这将使对象处于激活状态.没有优雅的方法来模拟用户在对象外部单击以取消选择它.解决方法是在其自己的窗口(而不是就地)中打开对象并关闭该文件窗口，或者尝试将对象激活为特定的、不存在的类.由于这会触发错误，因此必须将其包装在On Error Resume Next"和On Error GoTo 0"中.

Note that this will leave the object in an activated state. There's no elegant way to emulate the user clicking outside the object to de-select it. The workarounds are to either open the object in its own window (instead of in-place) and close that file window OR to try to activate the object as a specific, non-existant class. Since this will trigger an error, this has to be wrapped in `On Error Resume Next' and 'On Error GoTo 0'.

Sub test()
        Dim wdApp As Word.Application
        Set wdApp = CreateObject("word.application")
        wdApp.Visible = True ' for testing
        Dim wdAppendixB As Word.Document
        Set wdAppendixB = wdApp.Documents.Add(ThisWorkbook.Path & "\Templates\form_template.dotx")
        Dim wbAppB As Excel.Workbook
        Dim of As Word.OLEFormat
        Set of = wdAppendixB.InlineShapes.Item(1).OLEFormat
        of.Activate '.Edit
        Set wbAppB = of.Object
        wbAppB.Sheets("Sheet1").Range("B1").Value = "2019-06-02"
        On Error Resume Next
        of.ActivateAs "This.Class.NotExist"
        On Error GoTo 0
End Sub

这篇关于公开 Excel InlineShapes 进行编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！