Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Save workbook with today's date at the end

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Guys,

    Can anyone tell me how I can save the workbook with today's date at the end of it?

    I have a message asking if today is the last entry for the week. If yes I want the workbook saved as motor19/04/02. Is this possible? It could be motorApr192002 or anything that would identify today's date.
    I can't seem to get pass this point.
    Any help would be appreciated.

    Thanks

    Carol

    [ This Message was edited by: slimpickens on 2002-04-19 07:40 ]

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    The code go's in "ThisWorkbook." Note: you will need to hardcode your file name with a "-" at the end below!

    This code will, each time you open the file, save the file with the date at the end of the file name. Hope this helps. JSW

    Private Sub Workbook_Open()
    Dim MyDate
    Dim MyMonth
    MyDate = Date
    MyMonth = Month(MyDate)
    ThisWorkbook.SaveAs Filename:="YourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date)
    End Sub

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello JSW,

    I was sort of on the right track with my code, thanks for your help. I used this in a commandbutton on my form and it is working to a certain extent. I substituted the SaveAs for SaveCopyAs. Let Me Explain.

    I have on CommandButton2 -
    If vbyes then
    CommandButton3_Click

    This is working fine up to the point where I want to have my original document "motor" closed and left blank with just the column headings for the next weeks entries.

    The code I have for CommandButton3 is

    Dim MyDate
    Dim MyMonth
    MyDate = Date
    MyMonth = Month(MyDate)
    ThisWorkbook.SaveAs Filename:="YourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date)
    ThisWorkbook("Sheet1").Range("A5:V50").ClearContents
    End Sub


    Am I using this piece of code correctly??

    Carol

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    The code will save the original workbook as the name and date. The old workbook will now be untouched all your future work will go into the current workbook, the one with the date at the end.

    If you set up your master workbook as a template, that is a standard workbook with all the required formating, not a true Excel template. Then the code you are using will keep the pseudo-template workbook blank for the next time.

    When building your pseudo-template workbook, get it the way you want it, then add the auto-save-re-name code. Or, rename your best version, your master name and delete all the bad copies. Then your application should be ready for production.

    If you use a date named workbook you will get a workbook with two or more names at the end. JSW

    [ This Message was edited by: Joe Was on 2002-04-22 18:27 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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