VBA to append current date to file name during Save As

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VBA to append current date to file name during Save As

  1. #1
    Board Regular
    Join Date
    Dec 2007
    Location
    Oakhurst, New Jersey, USA
    Posts
    459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA to append current date to file name during Save As

     
    Dear All,

    I am looking for VBA to do a Save As on the Excel file and append the current date to the end of the file name.

    For example, if the file name is "Accounts Payable" and the current date is 18 July 2011, I would like the Save As file name to be "Accounts Payable 20110718" (YYYYMMDD sequence for the date).

    Also, if the user selects Cancel from the Save As dialog box, I do not want the macro to return a Run-Time Error 1004 as it does now. I would like it to cancel the Save As operation and continue on in the macro.

    Here is my current code:

    Code:
        'Save file
     
            ChDir "Z:\Excel"
        ActiveWorkbook.SaveAs Filename:="Z:\Excel\ACCOUNTS PAYABLE.xlsx", FileFormat _
            :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
            False, CreateBackup:=False
    I am using Windows 7 and Excel 2010

    Thanks,

    GL

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to append current date to file name during Save As

    Try this - you shouldn't need the ChDir statement

    Code:
    s = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs Filename:=Left(s, Len(s) - 5) & " " & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=51
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Location
    USA USA USA USA
    Posts
    556
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to append current date to file name during Save As

    Code:
        'Save file
     
            ChDir "Z:\Excel"
        ActiveWorkbook.SaveAs Filename:="Z:\Excel\ACCOUNTS PAYABLE & Format(Date, "yyyymmdd") & ".xlsx", FileFormat _
            :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
            False, CreateBackup:=False
    See if that works for you
    Last edited by Desu Nota from Columbus; Jul 18th, 2011 at 01:46 PM.
    "Frankly my dear, I don't give a ****." --Rhett Butler

    If posting code, use
    code tags...

    All posts based on Excel 2003

  4. #4
    Board Regular
    Join Date
    Dec 2007
    Location
    Oakhurst, New Jersey, USA
    Posts
    459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: VBA to append current date to file name during Save As

    Exactly what I was looking for!! All that was needed was a little concatenation and formatting.

    VoG, can you explain what "FileFormat:=51" is and why you used it instead of "FileFormat:=xlNormal" as recorded by the macro recorder?

    Thanks,

    GL

  5. #5
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to append current date to file name during Save As

    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    New Member
    Join Date
    Jul 2012
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to append current date to file name during Save As

    Hi VoG.
    My question is a little different. How would I change the code you posted to save the file as "A1 & current date".xlsm.

  7. #7
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to append current date to file name during Save As

    Quote Originally Posted by josh1981 View Post
    Hi VoG.
    My question is a little different. How would I change the code you posted to save the file as "A1 & current date".xlsm.
    Try like this - change the sheet name to suit

    Code:
    ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("A1").Value & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=51
    HTH, Peter
    Please test any code on a copy of your workbook.

  8. #8
    New Member
    Join Date
    Jul 2012
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to append current date to file name during Save As

    Thanks.

  9. #9
    New Member
    Join Date
    Jul 2012
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to append current date to file name during Save As

    Hi VoG. I wounder if I can extend my question. Everytime I update the file I will save it with the current date as part of the file name. I will change the code you posted a little so it saves the file eg.:
    C:\Dropbox\Ships\A1 190812.xlsm
    Code will be:
    ActiveWorkbook.SaveAs Filename:="C:\Dropbox\Ships\A1" & Format(Date, "ddmmyy") & ".xlsm", FileFormat:=51

    What I would like to do is delete the file with the older date as part of the file name so I dont end up accumlating a whole bunk of folders in the "Ships" file. Is this possible?

  10. #10
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to append current date to file name during Save As

      
    Maybe like this but proceed with caution - Kill will not move the file to the recycle bin. Also for .xlsm fileformat should be 52

    Code:
    On Error Resume Next
    Kill "C:\Dropbox\Ships\" & Range("A1").Value & "*.xlsm"
    On Error GoTo 0
    ActiveWorkbook.SaveAs Filename:="C:\Dropbox\Ships\" & Range("A1").Value & Format(Date, "ddmmyy") & ".xlsm", FileFormat:=52
    HTH, Peter
    Please test any code on a copy of your workbook.

User Tag List

Tags for this Thread

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
  •  

 

 
DMCA.com