I am trying to use VBA code written in MSWord to access an embedded spread sheet. I can't find any documentation on how this is suppose to work. Does word access the sheet through an instance of excel Is the sheet part of the document so I can find it in the "ThisDocument " I have tried everything I can think of, so any help would be appreciated.
Andy -
I've just used these:
activedocument.InlineShapes(2).OLEFormat.doverb
activedocument.Range(1,1).Select
The OLE object need not remain activated to keep working on it, so I use DoVerb and the following line right at the top of the code that manipulates the OLE object.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______
Andy -
I haven't done this in a while, so I may have skipped a step or two. I only tested it in the Immediate Window, which is like stepping through the code.
Word is more flaky than PowerPoint for programming to an embedded OLE object. PowerPoint doesn't make you activate the object.
In 2007, this only works on objects which were inserted using Insert tab > Object button. If you paste a chart into PowerPoint or Word from Excel, VBA has no way to get to the chart within the shape. And PowerPoint has lost its macro recorder (I haven't checked Word yet).
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______
Andy Pope, I discovered this thread while attempting to accomplish a similar task to the original post. One question - the routine works fine except it seems to leave an Excel.exe process running, i.e. the .Quit statement doesn't clear the process.
Thanks,
OM Gang
Thanks for the reply! I am incorporating the logic into a routine I am working on and will experiment with killing the excel process. I'll post back if I find pertinent information.
OM Gang
Andy, I've been working on the issue and the routine does leave an Excel.exe process running most of the time - but not always. I did some searching on the Excel.exe process and discovered this pertaining to Excel 2000
http://support.microsoft.com/default.aspx scid=kb;en-us;199219&Product=xlw2K
I decided to give it a try even though I am using Excel 2003. It appears getting rid of the With blocks and explicitly referring to the objects instead resolves the issue.
'select embedded worksheet object in document
Set objILS = objWordDoc.InlineShapes(2)
'open the embedded worksheet object for editing
objILS.OLEFormat.DoVerb wdOLEVerbHide
'create instance of embedded worksheet application
'(MS Excel) so we can manipulate the object
Set objExcelApp = objILS.OLEFormat.Object.Application
'select the specific workbook and worksheet we
'want to update
Set objWksht = objExcelApp.Workbooks(1).Worksheets(1)
My testing so far indicates this works.
OM Gang
To use macros in 2007 you have to go to ¡°word options¡± and select ¡°Show developer tab in the ribbon.¡±
I am using ¡°msoEmbeddedOLEObject¡± instead of ¡°InlineShapes¡± because the spread sheets use the ¡°In front of text¡± layout, and are no longer inline. As this isn¡¯t documented many places I expect it will be of use to someone.
I am now having a strange problem when I activate the shapes, their size changes from something other than 100%x100%. If I double click on the sheet (activate it for editing) and then click off it everything resizes the way it is suppose to be. I think there is a problem when using ¡°.OLEFormat.DoVerb wdOLEVerbHide¡± that causes the object to be resized to fit the added column and row headers. Does anyone know a way to rest the size of the sheet to 100% through VBA