Trisped


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.




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

Andy Pope


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






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

Trisped

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)





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

Andy Pope

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





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

Trisped

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



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

Jon Peltier

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
_______





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

Andy Pope

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!





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

Jon Peltier

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
_______





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

OM Gang

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





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

Andy Pope

Hi OM Gang,

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







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

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





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

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





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

Andy Pope

Nice piece of detective work ;)







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

Trisped

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