Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    There is 'OLEFormat.Activate', but no 'Deactivate'...

    Hi all,

    In the following code for saving a Word object embedded in Excel, you have to first activate the Word object, run it and make it visible. But how do you deactivate the Word object to close it automatically?

    TIA

    avraham
    Last edited by macropod; 2012-03-13 at 22:20. Reason: Merged thread: deleted code duplication (see post#2)

  2. #2
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Best way to save an embedded Word doc?

    Hi all,

    I will ask my question in a better way than the previous thread: Activate/Deactivate an OLE object.

    What is the best way to save an embedded Word doc? So far I found this code.But it has to open the embedded Word doc and then leaves it open.

    TIA

    -avraham

    Code:
    Sub SaveEmbedded()
    Const wdFormatDocument = 0
    Dim sh As Shape
    Dim objWord As Object ''Word.Document
    Dim objOLE As OLEObject
    Dim wSystem As Worksheet
    On Error Resume Next
    Set wSystem = Worksheets("System")
    ''The shape holding the object from 'Create from file'
    ''Object 2 is the name of the shape
    Set sh = wSystem.Shapes("Object 1")
    ''Activate the contents of the object
    sh.OLEFormat.Activate
    ''The OLE Object contained
    Set objOLE = sh.OLEFormat.Object
    ''This is the bit that took time
    Set objWord = objOLE.Object
    ''Easy enough
    objWord.SaveAs Filename:="Word_WaterReportDoc.doc", FileFormat:=wdFormatDocument
    sh.OLEFormat.Delete
    End Sub
    Last edited by macropod; 2012-03-13 at 22:17. Reason: Added code tags

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Avraham,

    Welcome to the lounge as a poster.

    I don't know about the "Best Way" but you can close the Word Document by adding 2 lines to your code above.
    objWord.Quit
    Set objWord = Nothing

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks!

    avraham

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts
    You should be able to put

    objWord.Quit

    between the SaveAs and the OLEFormat.Delete. You'll probably need a delay before the Quit to allow the SaveAs to finish first.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi RG,

    I finally got around to checking this. Sorry about the delay.

    Unfortunately, the extra two lines you suggest did not close the
    Word Document.

    Any more ideas? Maybe somehow get a handle to the Word app?

    Thank in advance,

    Avraham



  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Avraham,

    Sorry they didn't work. I don't know why because they work for me in an Access VBA program to write documentation out to a Word file. Maybe someone else has an idea.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Avraham,

    Does:

    objWord.Close

    not work in this case?

    (By the way your choice of a variable name is a little confusing - normally objWord would be used to refer to a Word application object, not a Word document object.)

    Gary

  9. The Following User Says Thank You to Gary Frieder For This Useful Post:

    amakeler (2012-03-15)

  10. #9
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    >> Does:
    >> objWord.Close
    >> not work in this case?

    Bingo! That works.


    >> (By the way your choice of a variable name is a little confusing - normally objWord would be used to refer to a Word application object, not a Word document object.)

    I got the code block off from some site on the Internet ...

    So, would you say that the
    objWord should instead be declared as:

    Dim objWordApp As Object ' Word App

    ?

    -avraham


  11. #10
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Glad that worked. (Sorry for delay in responding - going through a busy stretch!)

    No, I'm saying that since you're declaring a variable for a Word document, give it a variable name that identifies it clearly as a document, like "objWordDoc". Using the variable name "objWord" was confusing, because normally that is used to identify the Word application, not a Word document.

    Gary

Posting Permissions

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