以编程方式更新嵌入的 Excel 文件

发布于 2024-10-25 09:17:17 字数 941 浏览 6 评论 0原文

我正在尝试以编程方式修改 Word 文档中嵌入的 Excel 表格。为此，我修改了 docx 文件和嵌入的 Excel 文件。

主文档的重要部分如下：

<w:object w:dxaOrig="8406" w:dyaOrig="2056">
  <v:shape id="_x0000_i1028" type="#_x0000_t75" 
    style="width:390.75pt;height:95.25pt" o:ole=""><v:imagedata r:id="rId14" 
    o:title=""/>
  </v:shape>
  <o:OLEObject Type="Embed" ProgID="Excel.Sheet.12" ShapeID="_x0000_i1028" 
    DrawAspect="Content" ObjectID="_1349794876" r:id="rId15" 
    UpdateMode="Always"/>
</w:object>

Word 文档使用 OLEObject 链接到嵌入的 Excel 文档。出于显示目的，使用 .wmf 文件（使用 v:shape 元素）。我修改了 Excel 文档，该文档使此预览过时。

这会导致文档中出现一些奇怪的行为：

嵌入 (excel) 表格的预览显示错误的数据
双击嵌入表格会在嵌入的 excel 中打开表格并显示正确的数据
关闭嵌入编辑器会触发生成新预览，显示正确的数据

当然，我希望表格在打开文档时显示正确的表格。如何触发Word放弃图像并重新绘制预览？

对我来说，一个理想的解决方案是仅通过修改 docx 的内容来触发预览的重新生成，但使用小脚本的解决方案也会有所帮助。

原文

I'm trying to modify an embedded excel table in a word document programmatically. To do this, I have modified the docx file and the embedded excel file.

The significant part of the main document is the following:

<w:object w:dxaOrig="8406" w:dyaOrig="2056">
  <v:shape id="_x0000_i1028" type="#_x0000_t75" 
    style="width:390.75pt;height:95.25pt" o:ole=""><v:imagedata r:id="rId14" 
    o:title=""/>
  </v:shape>
  <o:OLEObject Type="Embed" ProgID="Excel.Sheet.12" ShapeID="_x0000_i1028" 
    DrawAspect="Content" ObjectID="_1349794876" r:id="rId15" 
    UpdateMode="Always"/>
</w:object>

The word document uses an OLEObject to link to the embedded excel document. For displaying purposes,
a .wmf file is used (using the v:shape element). I have modified the excel document, which outdated this preview.

This results in some strange behaviour in the document:

The preview of the embedded (excel) table shows the wrong data
Double clicking on the embedded table opens the table in an embedded excel and shows the correct data
Closing the embedded editor triggers the generation of a new preview, showing the correct data

Of course, I want the table to show the correct table when the document is opened. How can I trigger Word to discard the image and redraw the preview?

An ideal solution for me would be to trigger the regeneration of the preview just by modifying the contents of the docx, but solutions using a small script would also help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

临风闻羌笛 2024-11-01 09:17:17

对此没有完美的解决方案，但大多数情况下有效的解决方案是强制打开/关闭OLEFormat.Object。无论您是从 Word 外部重新填充嵌入的 Excel 工作表（即操作 Open XML 格式）还是通过对象模型进行，都没有关系。它涉及到从 Word 中打开嵌入的 Excel 电子表格，然后关闭该对象，以便将图像更改为嵌入电子表格中的当前值并创建新图像。

这在一定程度上取决于您是在多个 Word 文档上执行此操作还是仅在一个 Word 文档上执行此操作。在前一种情况下，使用全局模板（例如，normal.dot 或您创建并放入 STARTUP 文件夹中的自定义模板），或者在后一种情况下，只需在一个文档后面运行代码。两者都有不同的运行方式，但本质上您将挂钩 Document_Open 事件，并从那里检查当前文档是否具有 OLE 嵌入式对象，如果有，则打开和关闭它们。

正如我所说，如果不陷入困境，代码就不漂亮。基本上是因为它使用 SendKeys。大多数时候，这会起作用。有些时候，它不会。这就是 SendKeys 和其他未经同意而获得焦点的程序（例如即时通讯程序）的本质。

如果 Word 具有焦点（这意味着您无法打开 VBE 并按 F5），则此代码应该可以解决问题：

Sub UpdateEmbeddedXLSX()
Dim workbook As InlineShape
    For Each workbook In ActiveDocument.InlineShapes
        With workbook
            If .Type = wdInlineShapeEmbeddedOLEObject Then
                ''# Excel.Sheet.12 for Excel 2007
                If .OLEFormat.ClassType = "Excel.Sheet.12" Then
                    ''# Open Object as spreadsheet
                    .OLEFormat.DoVerb wdOLEVerbPrimary
                    ''# If you want, you can also do any updates here like
                    .OLEFormat.Object.ActiveSheet.Cells(2, 2).Value = ".1"
                    ''# Nasty - but it works - SendKeys
                    SendKeys "{ESC}", True
                End If
            End If
        End With
    Next
End Sub

至少，您可以将此代码放入您的 normal.dot 中，然后将其分配给 QAT 以作为宏运行。

请注意，代码不会绕过 Excel 打开、值更改然后关闭 - 这是使用嵌入对象的重要组成部分。使用链接而不是嵌入将是完成所有这一切的更顺利的方法，但我意识到这并不总是一种选择。

There is no perfect solution to this, but one that works most of the time is to force an open/close of the OLEFormat.Object. It doesn't matter if you are rehydrating the embedded Excel worksheet from outside of Word (i.e. manipulating the Open XML format) or doing it through the object model. What it involves is opening the embedded Excel spreadsheet from within Word and then closing that object for the image to be changed to the current values in the embedded spreadsheet and the new image to be created.

It depends just a bit if you are doing this on many Word documents or just one. In the former case, a global template (such as normal.dot or a custom one you create and put in the STARTUP folder) or in the later case, just run code behind of one document. Both have a different way to getting things to run, but essentially you will be hooking the Document_Open event and from there checking if the current document has OLE Embedded objects and if so, opening and closing them.

Without going into the hook, like I said, the code isn't pretty. Basically because it uses SendKeys. Most of the time, this will work. Some of the time, it won't. That is the nature of SendKeys and other programs receiving the focus without consent (such as an instant messenger program).

If Word has the focus (which means you can't open the VBE and press F5), this code should do the trick:

Sub UpdateEmbeddedXLSX()
Dim workbook As InlineShape
    For Each workbook In ActiveDocument.InlineShapes
        With workbook
            If .Type = wdInlineShapeEmbeddedOLEObject Then
                ''# Excel.Sheet.12 for Excel 2007
                If .OLEFormat.ClassType = "Excel.Sheet.12" Then
                    ''# Open Object as spreadsheet
                    .OLEFormat.DoVerb wdOLEVerbPrimary
                    ''# If you want, you can also do any updates here like
                    .OLEFormat.Object.ActiveSheet.Cells(2, 2).Value = ".1"
                    ''# Nasty - but it works - SendKeys
                    SendKeys "{ESC}", True
                End If
            End If
        End With
    Next
End Sub

At the very least, you could put this code in your normal.dot and assign it to the QAT to be run as a macro.

Note that the code doesn't get around Excel opening, the values changing and then closing - that is part and parcel of using embedded objects. Using linking instead of embedding would be a much smoother way to do all of this, but I realize it's not always an option.

回复收藏 0 原文

谁许谁一生繁华 2024-11-01 09:17:17

只是为了添加到旧帖子中，以防有人像我一样偶然发现这一点：

上面的代码效果很好，但我将其修改为使用书签而不是使用 SendKeys。 SendKeys 语句确实与我键盘上的 NumLock 混淆。这只是该命令的怪癖之一。

我所做的是在我的 Word 文档模板中创建书签。然后在我的代码中，我创建了一个指向书签的指针：

Dim bMark as bookmark
Set bMark as ActiveDocument.Bookmarks("NameOfBookmark")

然后，我执行了以下操作来代替 SendKeys 语句：

bMark.Range.Select
Selection.EndKey

这基本上将焦点从嵌入的工作表中拉出，转移到页面的书签上。然后 .EndKey 语句只是删除了选择。你实际上根本不需要它。

希望这有帮助！

Just to add to an old post in the event someone stumbles upon this like I did:

The above code works great, but I modified it to use bookmarks instead of using SendKeys. The SendKeys statement really messes with the NumLock on my keyboard. Just one of the quirks of that command.

What I did was create bookmarks in my Word Doc Template. Then in my code, I created a pointer to the bookmark:

Dim bMark as bookmark
Set bMark as ActiveDocument.Bookmarks("NameOfBookmark")

Then in place of the SendKeys statement, I did the following:

bMark.Range.Select
Selection.EndKey

This basically pulled the focus out of the embedded worksheet, and onto the bookmark of the page. Then the .EndKey statement simply removed the selection. You don't really even need it.

Hope this helps!

回复收藏 0 原文

夜血缘 2024-11-01 09:17:17

如果有人来这里寻找如何通过 VBA 宏更新嵌入 Visio 文档中的 Excel 对象而无需双击，则只需在嵌入工作簿上调用Save 即可。

Dim wb As Excel.Workbook
Set wb = ThisDocument.Pages("MyPageName").Shapes("NameOfWorksheetShape").Object
'Calling me updates the visible worksheets.
wb.Save

If anyone's coming here looking for how to update an Excel Object embedded in a Visio document via VBA macros without double-clicking, you just need to call Save on the embedded workbook.

Dim wb As Excel.Workbook
Set wb = ThisDocument.Pages("MyPageName").Shapes("NameOfWorksheetShape").Object
'Calling me updates the visible worksheets.
wb.Save

回复收藏 0 原文

~没有更多了~