Close document and Clear data

**athard** · 07-29-2010, 11:06 AM

I have a couple of questions here:

I created an excel doc where the user inputs certain data. The input fields are concatenated as per the requirements of the final deliverable. Then, when the user hits "Submit", the macro that i wrote copies the entire text, opens a word document, pastes the text there and saves the new word document.

Here are my 2 questions:

1. How can I automatically close word after the documents are created? I did appWD.Documents.Close but that only closes the document, but MS word is still open.

2. When the user exits excel, I want that the information they entered not be saved, so that when the next person goes in, they see an empty form which they fill out. How can I clean out that information?

Please help. Thank you.

**JBeaucaire** · 07-29-2010, 11:41 AM

1) Maybe:

appWD.Close

2) Maybe this in the ThisWorkbook module of the workbook that holds your form:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Close False
End Sub

**athard** · 07-29-2010, 12:02 PM

Thanks, I'll try the ThisWorkbook solution and let you know. I tried the appWD.close and it threw me an error.

**JBeaucaire** · 07-29-2010, 12:41 PM

I typically use a set of standard commands for opening an Word application instance and later closing it:

Dim RApath  As String
Dim wrdApp  As Object
Application.ScreenUpdating = False

Set wrdApp = CreateObject("Word.Application")
Set RArng = Sheets("Required RA").Columns("C:C").SpecialCells(xlCellTypeFormulas, 2)
Set PATHS = Sheets("RA Paths").Range("A3:B" & Rows.Count).SpecialCells(xlCellTypeConstants)

   RApath = Application.WorksheetFunction.VLookup("C:\", PATHS, 2, 0)
        With wrdApp.documents.Open(RApath)
            .PrintOut Background:=False, Copies:=1
            .Close False
        End With
    
wrdApp.Quit False
Set wrdApp = Nothing
Set RArng = Nothing