Save File as Diff. Name Each Time (2000/VBA)

Topic

New Reply

Hi,

I have a template file that I do an activeworkbook.save then manually Save As a different name each day in a specific directory. What’s the VBA code that allows you to do a Save As with the file name that varies?

Ex:

Original File name: Template 2000

Desired macro: Save As “MM.DD.YYYY.xls” (user inputs file name) in path location.

Hope this makes sense.

Thanks!

Reply | Quote

Replies

You could use something like the following :

Sub FileSave()
    Dim fPath as String, fName as String
    fPath = "C:MyFiles"
    fName = fpath & Format(Now(), "MMDDYYYY HHMM") & ".xls"
    ActiveWorkbook.SaveAs (fname)
End Sub

If you wish to use the date, you may not need user input. The above also includes the time (HHMM) which you may remove if it is not required. I think it is better to omit the periods in the date, as it has a special meaning in filenames.

You could paste similar code the the Workbook_BeforeSave event if you wished the operation to be transparent and automatic.

Andrew C

Reply | Quote