Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Date Stamping a save command. (Office XP)

    Hi,
    I see lots of backup utilities add an automatic date stamp to the proposed Name for the saved backup file. I would like to know if this is possible to do in the office applications. When saving an office file, how can I have the current date added as a suffix to the filename I propose ie: MyBudget:01-12-05.xls (where the bolded portion is added automatically!)
    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Stamping a save command. (Office XP)

    The Word Application object has a DocumentBeforeSave event, and the Excel Application object has a WorkbookBeforeSave event. Look up WithEvents to see examples of how to handle application level events.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Stamping a save command. (Office XP)

    Tx. I'll see if I can whip up an example tonight (when thigs are quiet) and post it back tomorrow. If I hit a glitch, I'll ask for some assistance!
    Cheers
    Regards,
    Rudi

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Stamping a save command. (Office XP)

    You have a glitch in your example! You can't use a colon in a file name, that is reserved to separate the device as in C:MyFile 2005-12-12.doc.
    Legare Coleman

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Stamping a save command. (Office XP)

    Tx for the good spot. The example was simple and example though. Cheers for the spot though Legare. I shall use a hash instead of the colon! <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>
    Regards,
    Rudi

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Date Stamping a save command. (Office XP)

    You can modify the code in <post#=283508>post 283508</post#> to tack on a date-time stamp instead of a version number.

    {word part} & Format(Now(), "yyyymmdd-hhnnss") & ".doc"

    or something along those lines...

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Stamping a save command. (Office XP)

    Jefferson, tx for this Word example. I was focussing on Excel in the thread, but this code will help to get the task done in Word. I'll tweek the code and see if I can get it to work for me!
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Stamping a save command. (Office XP)

    Regarding Excel:

    Please help me with the conditional in this code. If the WB is already saved then it must just update the file, but if it is not saved it must open the Save As Dialog and add the DateStamp which I seem to have working well!
    Tx

    <pre>Option Explicit
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim TimeStamp As String
    Dim myFile As String
    TimeStamp = "-" & Format(Date, "dd-mm-yy")
    myFile = Application.GetSaveAsFilename
    'If activeworkbook is already saved then
    ' ActiveWorkbook.Save
    'Else
    ChDir "C:Test"
    ActiveWorkbook.SaveAs Filename:= _
    myFile & TimeStamp & ".xls", _
    FileFormat:=xlNormal, _
    Password:="", _
    WriteResPassword:="", _
    ReadOnlyRecommended:=False, _
    CreateBackup:=False
    'End If
    End Sub</pre>

    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Stamping a save command. (Office XP)

    You can test if a workbook has been saved:

    If InStr(ActiveWorkbook.FullName, "") > 0 Then

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Stamping a save command. (Office XP)

    Great. Tx, I'll add that to the code I have.
    Cheers
    Regards,
    Rudi

Posting Permissions

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