Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    London, United Kingdom
    Thanked 0 Times in 0 Posts

    Date Stamp Save As (2000 SR-1)

    I would like to know if there is a way to make a template or file Save As, with a name of your choice but with the date, that has been inserted into a particular cell included at the end of the file name.

    For example: An expense template is used and the date of the expense form is entered into a cell, say B12. When the person does Save As, they will be forced to save the file with the date included in the file name.


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Date Stamp Save As (2000 SR-1)

    This code added to the thisworkbook object (NOT a normal module) should do what you want. Change the date format if desired.

    <pre>Option Explicit
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim filesavename
    filesavename = Application.GetSaveAsFilename( _
    fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    If filesavename <> False Then
    Application.EnableEvents = False
    filesavename = Application.WorksheetFunction. _
    Substitute(filesavename, ".xls", _
    Format(Range("b12").Value, "mm-dd-yyyy")) & ".xls"
    ActiveWorkbook.SaveAs filename:=filesavename
    Application.EnableEvents = True
    End If
    Cancel = True
    End Sub

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Thanked 0 Times in 0 Posts

    Re: Date Stamp Save As (2000 SR-1)

    I don't know how the user is supplying the rest of the file name (the part before the date) but I did a macro like this once for some sales reps and had a weird problem.

    The users got used to just hitting a button to save and my code would automatically build them a suggested file name based on customer name and the date. So when they started including characters like "/" or "?" in the customer name they got an error message. I had to add a little code to strip out any invalid characters from the suggested filename.

    Just something to consider if you are planning to "build" a file name out of available text on the spreadsheet. And if it's possible they will type something dumb like "Next Thursday" in the date cell you may have to consider that, too. I had that happen as well.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

Posting Permissions

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