Can you use VBA to access a spread sheet embedded in a word doc?

Can you use VBA to access a spread sheet embedded in a word doc?

• aarongreenberg

  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

  
  

• DmitryMS

  Yes thanks. Can you tell me what wdOLEVerbHide does and how it is different from wdOLEVerbPrimary
  
  Also, do wdOLEVerbs work with a msoEmbeddedOLEObject (what a non-inline spread sheet is)
  

• Paul Gielens

  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
  _______
  

  
  
  

• Becker2

  Hi Jon,
  
  When I test that it leaves the object active and selected. Unless I step through the code in the VBE in which case it does indeed quit it and select the range in the active document.
  
  All in all using automation on OLE objects within word is not a fun experience!
  
  
  

• tt2lhp

  Hi OM Gang,
  
  For me, in the very limited testing I did, the excel.exe thread was created and destroyed using that code.
  
  
  
  
  

• Jason D. Camp

  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

  
  

• EthanS

  The primary verb is to activate, same as clicking the object.
  You can use that but there is no code to deactivate the object when completed.
  
  Have not tried using verbs with a msoEmbeddedOLEObject, so can not say. Did you try
  
  
  

• davidguygc

  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
  _______
  

  
  
  

• MShah

  Nice piece of detective work ;)
  
  
  
  
  

• Armin Prosch

  I can't find any documentation on an msoEmbeddedOLEObject, and wdOLEVerbHide seems to work fine. Also, DoVerb is limited to wdOLEVerb___ enumerations.
  

• Jon Stroh

  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

  
  

• zolivier

  Hi,
  
  Something along these lines.
  
  Sub WriteToSS()
  
  Dim objSS As InlineShape
  
  With ActiveDocument.InlineShapes(1)
  .OLEFormat.DoVerb wdOLEVerbHide
  With .OLEFormat.Object.Application
  With .workbooks(1).worksheets(1)
  .Cells(1, 1).Value = "Hello"
  .Range("B1").Value = "World"
  End With
  .Quit
  End With
  End With
  
  End Sub
  
  

• WilliamVista

  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

  
  
• Can you use VBA to access a spread sheet embedded in a word doc?