VBA to append current date to file name during Save As

[General Ledger]

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:

    '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

 

[VoG]

Try this - you shouldn't need the ChDir statement

s = ActiveWorkbook.FullName
ActiveWorkbook.SaveAs Filename:=Left(s, Len(s) - 5) & " " & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=51

 

[Desu Nota from Columbus]

    '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

 

[General Ledger]

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<!-- / message --><!-- sig -->

 

[VoG]

This should explain http://www.rondebruin.nl/saveas.htm

 

[josh1981]

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.

 

[VoG]

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

ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("A1").Value & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=51

 

[josh1981]

Thanks.

 

[josh1981]

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?

 

[VoG]

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

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