There is no ideal solution for this, but most of the time it should be used to open / close OLEFormat.Object . It doesn’t matter if you are translating embedded Excel worksheets from outside the Word (i.e., manipulating the Open XML format) or doing it through the object model. It includes opening the embedded Excel spreadsheet from Word and then closing this object to change the image to the current values in the embedded spreadsheet and creating a new image.
It depends a little if you do this in many Word documents or in just one. In the first case, a global template (for example, normal.dot or a custom one that you create and put in the STARTUP folder) or in a later case, just run the code behind one document. Both have another way to make things work, but essentially you will hook into the Document_Open event and from there check if the current document has OLE Embedded objects, and if so, opening and closing them.
Without going into the hook, as I said, the code is not very good. Mostly because it uses SendKeys . In most cases this will work. For a while it will not be. This is the nature of SendKeys and other programs that receive focus without consent (for example, an instant messaging program).
If Word has focus (this means you cannot open 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 ''
At least you can put this code in your normal.dot and assign it a QAT to run as a macro.
Please note that the code does not apply to opening Excel, the values change and then close, which is an integral part of using built-in objects. Using links instead of nesting would be a smoother way to do all this, but I understand that this is not always an option.