Manually close Word doc which was opened by Excel VBA, but without save prompt

    Hello. Possibly an old theme, but with a slight variation.


    I'm doing the finishing touches on an Excel-based app which is used for basic statistics with form automation of mail and email. The one thing I cannot overcome is manually closing a Word document which was opened by the Excel app, auto-populated with data using bookmarks, and then the human user types in the required postal address.


    It has to be done in this manner as the users are required to read the document to check for errors, etc.


    I've tried myDoc.Saved = True but if any manual alteration is made after the form has been opened, Windows does the Save Y/N thing.


    Because Excel is the main program, I assume I can't add a module to insist that the save prompt or alerts for Word are turned off.


    I can't really add Buttons in the Excel form, as this is already cluttered.


    Any ideas on how to allow clicking the X to kill Word without the save dialogue?


    Altering settings in Office may not be possible due to admin rights - it's a workplace environment.


    Many thanks in advance.
    Rich

    Re: Manually close Word doc which was opened by Excel VBA, but without save prompt


    The simplest thing to do is use a template with the following code snippet in the ThisDocument class module

    Code
    Private Sub Document_Close()


    ThisDocument.Saved = True
    
End Sub


    That event is called when the user closes the document, but before Word starts to unload; sets the SAVED property to True and so suppresses the Save dialogue.


    If using a template is not an option, post a sample of your code that starts Word. Is is possible to trap events in Word using Excel and set the property to True when the user closes the document - however, it would be useful to see your existing code first...


    [COLOR="#000080"]If you do post code, please observe the rules here - use code tags (Click the link for a brief description)[/COLOR]

    Re: Manually close Word doc which was opened by Excel VBA, but without save prompt


    Thanks for the reply, Cytop.


    I don't know if the template approach is viable, and because I'm using an Excel-based app as the only actual VBA program, there is no ThisDocument class module; only a ThisWorkbook for the Excel. At present, all the Word docs are simply pages with text, tables and bookmarks, the latter being for the relevant data from the user form in Excel.


    The code I've used to open the Word document(s) is as follows:


    Code
    Set objWord = CreateObject("Word.Application")
        Set docWord2 = objWord.Documents.Open("H:\My Documents\AF3Letter.doc", ReadOnly:=True)
        'Set docWord2 = objWord.Documents.Open("C:\Users\Bert\VBA STUFF\AF3Letter.doc")
        'objWord.Activate
        objWord.Visible = True
        objWord.Activate


    At the bottom of the sub I've added

    Code
    docWord2.Saved = True


    ...but if the slightest alteration is made to the form once it's been populated from Excel and then opened, Windows obviously notices this change and asks for save y/n upon closing the document.


    The other idea I have explored is looking at killing the instance of Word once the form has been printed, but what little I have read in various threads seems to indicate this is quite tricky. Other threads say to use a module for the instance of Word and include code to convince Windows that the doc has been saved, etc.


    I'm totally open to any ideas. Anything is enormously appreciated.

    Re: Manually close Word doc which was opened by Excel VBA, but without save prompt


    For this to work, you must add a reference to the Word Object library using the Tools/References menu - look for Microsoft Word 12.0 Object Library (The 12.0 will change depending on the installed version of Office)


    Add a new CLASS module to the Excel project. Leave the name as the default CLASS1. Copy the following code to the class


    Your code changes slightly too...


    If you close the workbook before the Word document, then the event will not fire...

    Re: Manually close Word doc which was opened by Excel VBA, but without save prompt


    Thank you again. I shall test this on my workplace PC first thing tomorrow morning and post the results.

    Re: Manually close Word doc which was opened by Excel VBA, but without save prompt


    Hello again.


    For some reason (security policy at work?) the Tools>References is greyed out at work, so I've tried adding your very kindly provided code on my PC at home, where this is not greyed out, after having added the MS Word Library reference.


    It hasn't removed the save y/n dialogue box, so I'm concluding I've added the actual code in the wrong place(s).


    The first block I added inside a newly inserted Class Module as per your instructions.


    The second block is where I believe I'm going astray. Where should I add the bit:


    Code
    Option Explicit     '// REQUIRES A REFERENCE TO MICROSOFT WORD xx.x OBJECT LIBRARY  '// Use the Tools.References menus...    '// These needs to be scoped to the module so they do not go out of scope Dim objWord As Word.Application  Dim oCLASS As Class1


    ...? Where you have written it should be scoped to the module, do you mean the Class Module?


    From a more theory-oriented perspective, what subject matter would you recommend I read up on? I'm very keen to understand the whys and wherefores behind the code.


    Thanks again,
    Rich

    Re: Manually close Word doc which was opened by Excel VBA, but without save prompt


    What I posted for the class module is all that goes in the class module - what I meant by scoped to the module is that the variables should be declared in the General Declarations section of the module containing the main procedure.


    The reason for the class is that a variable declared as WithEvents can only be used in a class module. Class modules are basically everything except code modules - class modules (obviously), Worksheet code modules and userforms.


    Also, WiithEvents requires the object being declared to be early bound - in other words, you must add it using the Tools/References menu. You cannot declare it as a generic object (Late bound). If security policies at your workplace prohibit early binding then you have a problem. You cannot use the code. You need to check that with your network admin.


    As a demo, I'll knock up a simple app using WithEvents later or, more likely, sometime tomorrow.

    Re: Manually close Word doc which was opened by Excel VBA, but without save prompt


    Thanks enormously, Cytop. It was extremely kind of you to go to all of this trouble. I'll have a chat with our network people about the policy on early binding. Seeing as it's entirely a work-specific project, I suspect there'll be some flexibility. Thanks again. Rich.

    Re: Manually close Word doc which was opened by Excel VBA, but without save prompt


    Just to confirm: your solution has worked most magnificently, and the inclusion of the debugging code was thoughtful. Thank you again, Cytop. I shall spend some time reading up on WithEvent.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login