Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Object in Word (VBA/Excel/2k3)

    I am trying to place an Excel ws in a Word document (via Excel/VBA). I would like for the ws to be placed at a particular bookmark in the document and I would also like to be able to transfer data to the Excel object. I think that I can do the latter part once the object has been created, but I am not seeing exactly how to start. How can I accomplish this? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Object in Word (VBA/Excel/2k3)

    Something like this?

    ActiveDocument.Bookmarks("Test").Range.InlineShape s.AddOLEObject ClassType:="Excel.Sheet.8"

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Object in Word (VBA/Excel/2k3)

    Yes, that works. Thanks. Why "...Sheet.8"?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Object in Word (VBA/Excel/2k3)

    Excel.Sheet.8 is the class type of an Excel worksheet (the way it is registered in the HKEY_CLASSES_ROOT section of the Windows Registry). The number 8 indicates that it is an Excel 8 aka Excel 97 sheet - the file format for Excel hasn't changed between Office 97 and Office 2003

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Object in Word (VBA/Excel/2k3)

    Okay, above my head.

    I thought I would be able to easily reference a range in the sheet (e.g., a1:a10), but I am lost on the syntax. Any help? Last question I promise. Thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Object in Word (VBA/Excel/2k3)

    You can use code like this:

    Dim sh As InlineShape
    Set sh = ActiveDocument.Bookmarks("Test").Range.InlineShape s.AddOLEObject(ClassType:="Excel.Sheet.8")
    sh.OLEFormat.Object.ActiveSheet.Range("B2") = 3
    Set sh = Nothing

Posting Permissions

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