Embedded Excel object in Word

[Ganix]

Hello

In Word I have an embedded Excel object with several worksheets.

I am using Office version 2002.


Instead of having to go to the embedded Excel object page each time, I was wondering if I can use Macros to open embedded Excel worksheets.

This is all I need. To be able to open embedded Excel worksheets without having to go to the Word page were the embedded Excel object is.

Thank you for your help.


I am sorry but I don’t use the current version of Office as I find it just too hard to use.


PS

I need the VBA code please.

[macropod]

So how do you propose to open the object without that page activating? What is the purpose?

[Ganix]

I thought I might be able to do it in VBA code.

In VBA go to the page (if I need to) and use the option to open the worksheets so I can do the work I need to do. I just need to open the worksheets (quickly).

[macropod]

So how would Word know which of your several worksheets to open?

[Ganix]

If I can’t go to a worksheet is there VBA code to open an embedded Excel object.

When I use the embedded Excel object it goes to the first worksheet.

[Ganix]

Do I take it then I can't do the above in VBA?

[macropod]

Why not just use a split window, with your embedded worksheet in one and whatever other range you're working on in the other?

[Ganix]

Thank you

I am using a split window in one of my worksheets.


I repeat my post above:

Do I take it then I can't do the above in VBA (open an embedded Excel object (which defaults to the first worksheet))?

[macropod]

I said nothing about a split window in your worksheet, which is entirely irrelevant. No-one is saying opening an embedded worksheet can't be done with VBA, but I have to wonder what the point is when you don't even know which embedded worksheet you want to open. Once one starts building sufficient logic into the code to handle that, it ends up being no faster than doing it without a macro.

[Boris_R]

Try

Sub EditEmbeddedExcel()
Dim shapesAll As Shapes


Set shapesAll = ActiveDocument.Shapes
If shapesAll.Count >= 1 Then
If shapesAll(1).Type = msoEmbeddedOLEObject Then
With shapesAll(1).OLEFormat
.ActivateAs ClassType:="Excel.Sheet"
.Edit
End With
End If
End If
End Sub


Lower a fragment from the Word Developer Reference
Word Developer Reference
OLEFormat Object
Represents the OLE characteristics (other than linking) for an OLE object, ActiveX control, or field.
Remarks


Use the OLEFormat property for a shape, inline shape, or field to return the OLEFormat object. The following example displays the class type for the first shape on the active document.

Visual Basic for Applications
MsgBox ActiveDocument.Shapes(1).OLEFormat.ClassType

Not all types of shapes, inline shapes, and fields have OLE capabilities. Use the Type property for the Shape and InlineShape objects to determine what category the specified shape or inline shape falls into. The Type property for a Field object returns the type of field.

You can use the Activate, Edit, Open, and DoVerb methods to automate an OLE object.

Use the Object property to return an object that represents an ActiveX control or OLE object. With this object, you can use the properties and methods of the container application or the ActiveX control.


© 2010 Microsoft Corporation. All rights reserved.

[Ganix]

Thank you

Boris_R your code didn’t do anything, but thank you for it.


I think we are over complicating this.

At the moment when I go to the page the embedded Excel object is on
I right click on the Excel object
and click on the option Worksheet Object
next I click on option Open.

I am now in Excel.


Forget the Worksheet (if possible).


I ask again is it possible to use VBA to use the option Open to go into Excel to get to my Worksheets?

I just need to use VBA to Open Excel to get to my Worksheets.

Why is this hard work to do, or to get help with?