The macro below is intended for inspecting all the embedded objects in a Microsoft Word document. When run, it opens each of these objects if possible, regardless of type, and leaves the corresponding editing window open. This allows last minute changes to be made conveniently before the objects are saved and refreshed in the document view. A similar approach can be used for objects embedded in Excel or PowerPoint, though the references are a little different.
This macro could also be used to test quickly whether a large document has embedded objects to be dealt with. Sometimes it's hard to recognize these. It does not, however, open bitmaps inserted as pictures.
Sub openEmbeddedObjects()
Dim longShapeCount As Long
On Error Resume Next
Application.ScreenUpdating = False
longShapeCount = ActiveDocument.InlineShapes.Count
If longShapeCount > 0 Then
For i = 1 To longShapeCount
ActiveDocument.InlineShapes(i).OLEFormat.Edit
Next
End If
Application.ScreenUpdating = True
End Sub
Hi Kevin,
ReplyDeleteHow would you adapt this for PPT files?
Paul
By adapting to the PPTX object model if this doesn't fit.
DeleteThanks for the quick reply. That seems to be about where I get out of my depth.
DeleteI just upgraded to MemoQ 2014 to translate the embedded text, but now when I try to open them to refresh them in the exported PPT, all of the text disappears from the slide, although it is translated in the embedded Excel. Has this ever happened to you?
Paul
That's because of the refresh issue addressed here. DVX ships with a macro to do this; I mentioned a few times to Kilgray's developers that they woulds face this but I think they got busy and forgot. Send a bitch note to Support. Meanwhile, manual opening of the object in the translated PPTX should fix things.
DeleteSomething like this should work for PPTX.
DeleteI've only tested it briefly, and it's a bit rough around the edges (one should really hide all the blinking edit windows if possible),
but it might do the trick. Not sure if it needs adapting to run on old style PPT files.
Sub openEmbeddedObjects()
Dim sld As Slide
Dim shp As Shape
For Each sld In ActivePresentation.Slides
For Each shp In sld.Shapes
If shp.HasChart = msoTrue Then
If Not shp.Chart.ChartData.IsLinked Then
shp.Chart.ChartData.Activate
shp.Chart.ChartData.Workbook.Close
End If
End If
Next
Next
End Sub
Thank you :-)
DeleteCheers Kevin,
ReplyDeleteI submitted a support ticket and just waiting to hear back.
When I open them manually, all of the text disappears... I just compared it to the source file and the cells are white rather than the original shade of blue/red. I hope I'm not missing something obvious.
Hm. Sounds like I need to make more test files. I just planned a new tutorial last night with a similar case, so I might have to modify my plans.
DeleteIn case it's useful: I can copy the contents of the exported embedded Excel file into the source document embedded Excel file and it updates the graph as it should. Something seems to be lost in the conversion.
DeleteFor what it's worth, I can copy the contents of the exported embedded file into the embedded file in the source ppt, and it shows up as a graph perfectly. It seems like when exporting the Excel file, the graph formatting is lost. I'm not a competent enough spreadsheet user to fix this so had to copy & paste manually, but could be worth looking into.
DeleteHi Kevin,
ReplyDeleteThanks for the tip on translating embedded objects, it works great!
I do run into a problem when I try to add a macro to open or examine all embedded objects so that they are refreshed within the Word document.
I try the macro you propose, and it won't let me run it, there's an error everytime.
What I'd like to do is this :
Use a macro to simply refresh all embedded objects, not open them. Or if they have to be open, have the macro close them automatically.
Do you think that is possible? Could you suggest a macro to do this? I have limited knowledge of VBA and wasn't able to create the macro.
Thanks!