File beforeSave Event

Megazoid · #1 09-12-2014, 05:03 AM

Hi guys, this is my first visit to this forum so please forgive me if I unknowingly break any site rules. I will try to honor all of these.

I have some knowledge of programming Excel using VBA but very little knowlege of using VBA with Word.

I have a difficulty and I am not sure wether to log this as a Word Issue, Excel Issue or a Windows Issue, I am hoping somebody can provide me with some guidance.

I have an excel macro, which during its execution, opens a blank word template document and transfers data to this word document. I then want this document to be stored in a folder with other documents created in the same way. Each new document should have a unique file name which starts with a serial number. All of this is catered for and operates correctly. Where I have a difficulty is that it is possible for human error to come into effect and open a document for a file which already exists. So I have a word document BeforeSave event triggered which checks to see if the file already exists and warns the user if this is the case.
The beforesave event is stored in a class and also works fine when I am dealing directly with Word. i.e if I open the word document as a word document and not via Excel.It always triggers and testing advises the user if they would overwrite an already existing file.Here comes the rub. If I get the Excel Macro to open the word document and use the word Save icon the beforesave event does not trigger. Even if I close Excel leaving only the word document Open, the Word BeforeSave event still does not trigger.
Could anybody please provide me with any ideas to why this may be happening and how to tackle it. As I stated it works fine when I open Word on its own.

Many Thanks

Megazoid.

macropod · #2 09-12-2014, 02:54 PM

Instead of trying to use the BeforeSave event, why not just use the Dir function to test for the file's existence before saving?

gmayor · #3 09-13-2014, 12:28 AM

An alternative would be to ensure that an existing file is never overwritten by appending a number to the filename. The following two functions will between them perform both operations.

Code:

Public Function FileNameUnique(strPath As String, _
                               strFilename As String, _
                               strExtension As String) As String
Dim lngF As Long
Dim lngName As Long
    lngF = 1
    lngName = Len(strFilename) - (Len(strExtension) + 1)
    strFilename = Left(strFilename, lngName)
    Do While FileExists(strPath & strFilename & Chr(46) & strExtension) = True
        strFilename = Left(strFilename, lngName) & "(" & lngF & ")"
        lngF = lngF + 1
    Loop
    FileNameUnique = strFilename & Chr(46) & strExtension
lbl_Exit:
    Exit Function
End Function


Public Function FileExists(ByVal Filename As String) As Boolean
Dim lngAttr As Long
    On Error GoTo NoFile
    lngAttr = GetAttr(Filename)
    If (lngAttr And vbDirectory) <> vbDirectory Then
        FileExists = True
    End If
NoFile:
    Exit Function
End Function

Megazoid · #4 09-14-2014, 01:41 AM

Hi guys, both your responses have me doing some more thinking, so I would like to thank you for this, I appreciate the time that you have given towards providing a solution.
I guess as usual everything is about the logic being followed, which is not always that clear, and the timing as to when the logic is applied.
Once the Word template has been opened, there is data which is written to the template via the Excel file and some data which is written in by the user. One of the items is the next serial number for the document. The file name becomes serial number + name of user + event name + date created (dd.mm.yyyy).
There is also a hyperlink created to the saved document anchored to the serial number.
I guess to this another approach is simply to force the serial number and not to allow any modifications.

Thanks to you guys I now have some options to think about and pursue.

Best Wishes

Megazoid

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Workbook_Open event do not working?	beginner	Excel Programming	8	04-11-2013 02:37 PM
Duplicate event reminders	rgarneau	Outlook	0	01-23-2012 08:58 AM
Create calendar event	groegee	Outlook	1	12-05-2011 09:56 PM
Catch event before next slide	PetLahev	PowerPoint	0	10-21-2011 03:29 AM
BeforeClose BeforePrint BeforeSave	PosseJohn	Word VBA	2	07-17-2011 01:39 AM