Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modify an Excel document embedded in a Word doc (VBA for Word on XP2002 SP

    I'm writing a macro to modify a number of documents embedded in a Word document. I can access and modify the embedded Word documents using ActiveDocument.InlineShapes(1).Activate etc. However, there is also an Excel document embedded in the Word document, and I can open it using the code, but I can't figure out how to grab it so that I can modify specific cells in specific sheets.

    CreateObject gives me a new file, which I don't want, and GetObject needs the specific name of the document - there isn't one, because it's embedded in the Word document. I can't see how to tell the Word macro to perform Excel actions on the active document.

    Is this even possible? Perhaps I would be better off with an entirely different approach: saving the embedded Excel file to a temp location, working on it, deleting the embedded doc, re-embedding the new one, then deleting it from the temp location.

    I'd really appreciate some help on this one!

    Thanks.

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

    Re: Modify an Excel document embedded in a Word doc (VBA for Word on XP200

    Welcome to Woody's Lounge.

    You can use something like the following, but it's not perfect. It's relatively slow, and it leaves an invisible instance of Excel in memory until the document is closed, despite the instruction xls.Application.Quit.

    Sub ProcessXLS()
    Dim shp As InlineShape
    Dim xls As Excel.Workbook

    For Each shp In ActiveDocument.InlineShapes
    If shp.Type = wdInlineShapeEmbeddedOLEObject Then
    If Left(shp.OLEFormat.ProgID, 5) = "Excel" Then
    shp.OLEFormat.Activate
    Set xls = shp.OLEFormat.Object
    xls.ActiveSheet.Range("A1") = 37
    xls.Application.Quit
    Set xls = Nothing
    SendKeys "{ESC}"
    Selection.HomeKey Unit:=wdStory
    Exit For
    End If
    End If
    Next

    Set shp = Nothing
    End Sub

  3. #3
    New Lounger
    Join Date
    Aug 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify an Excel document embedded in a Word doc (VBA for Word on XP200

    Thanks - works a treat!

Posting Permissions

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