VBA to append current date to file name during Save As

General Ledger

Active Member
Joined
Dec 31, 2007
Messages
460
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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:
Upvote 0
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 -->
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top