Consulting

Results 1 to 11 of 11

Thread: Embedded Excel object in Word

  1. #1

    Embedded Excel object in Word

    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.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    So how do you propose to open the object without that page activating? What is the purpose?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    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).

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    So how would Word know which of your several worksheets to open?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    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.

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

  7. #7
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Why not just use a split window, with your embedded worksheet in one and whatever other range you're working on in the other?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    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))?

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  10. #10
    VBAX Newbie
    Joined
    Oct 2015
    Posts
    5
    Location
    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.
    Last edited by Boris_R; 06-27-2019 at 12:19 AM.

  11. #11
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •