Modules: Using WithEvents to get saved filenames

(Q)    I'm using Automation to start Excel/Word. I need to know the filenames and paths of all documents the user saved in that particular session of the application.  How can I retrieve this information before the Automation object is closed by the user?

(A)    Access 97 introduces WithEvents which allows us to respond to events triggered by an ActiveX Object.  Microsoft Excel exposes the WorkbookBeforeClose event which we can use to manually save any files.  All that's needed is a class module, cExcel.

Class cExcel

'************ Code Start **************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Public WithEvents mobjXLApp As Excel.Application

Private Sub Class_Initialize()
    Set mobjXLApp = New Excel.Application
    mobjXLApp.Visible = True
End Sub

Private Sub mobjXLApp_WorkbookBeforeClose( _
            ByVal Wb As Excel.Workbook, _
            Cancel As Boolean)
Dim strSave As String

    With mobjXLApp
        If Wb.Saved = False Then
            If Wb.Path = vbNullString Then
                strSave = .GetSaveAsFilename( _
                        fileFilter:="Excel Workbooks (*.XLS), *.XLS")
                If Not strSave = vbNullString Then
                    Wb.SaveAs strSave
                End If
            Else
                Wb.Save
                strSave = Wb.Path & "\" & Wb.Name
            End If
            Wb.Saved = True
        End If
        Debug.Print Wb.Path & "\" & Wb.Name
    End With
End Sub
'************** Code End ************

Now, we instantiate cExcel as

Public clsExcel As cExcel

Sub sExcelWithEvents()
    Set clsExcel = New cExcel
End Sub

The Class_Initialize procedure of the subroutine ensures that the automation object is created.  The WithEvents declaration allows us to "hook" the WorkbookBeforeClose event to the automation application object.

When we try to close a workbook in this Excel instance, our custom WorkbookBeforeClose event fires, allowing us to check whether the workbook needs to be saved, and if it hasn't been saved before, prompt the user to provide a SaveAs file name.   Then we set the Saved flag of the workbook to True, allowing Excel to close it without any prompts.

The same logic can be applied to Microsoft Word Automation objects.  However, since Word doesn't expose an equivalent event to WorkbookBeforeClose, we have to use the Close event of the Document object to save the file.

Note: This code only seems to work when you work with a single Document within Word.  I think that the DocumentChange event can be used to handle the situation when multiple documents may be created by the user, but as is obvious, I haven't yet found a way.  If you have any comments, I'll appreciate your feedback.

Class cWord

'*************** Code Start ***************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Public WithEvents mobjWordApp  As Word.Application
Private WithEvents mobjDoc As Word.Document

Private Sub Class_Initialize()
    Set mobjWordApp = New Word.Application
    Set mobjDoc = mobjWordApp.Documents.Add
    mobjWordApp.Visible = True
End Sub

Private Sub mobjDoc_Close()
Dim strSave  As String
Dim dlg As Word.Dialog
    
    With mobjDoc
        If Not .Saved Then
            If .Path = vbNullString Then
                Set dlg = mobjWordApp.Dialogs(wdDialogFileSaveAs)
                With dlg
                    .Display
                    strSave = .Name
                    .Update
                    .Name = strSave
                    .Execute
                    strSave = mobjDoc.Path & "\" & strSave
                End With
            Else
                strSave = .Path & "\" & .Name
                .Save
            End If
        End If
        .Saved = True
        Debug.Print strSave
    End With
End Sub

Private Sub mobjWordApp_DocumentChange()
   ' Set mobjDoc = mobjWordApp.ActiveDocument
End Sub
'*************** Code End ****************

The usage remains the same as with Excel.

Public clsWord As cWord

Sub sWordWithEvents()
    Set clsWord = New cWord
End Sub