Saving Excel Files (2000)

Topic

New Reply

How can I have a macro save the current file with the file name (“Save As”) as the current date ?

Reply | Quote

Replies

use :

Sub SaveAsDate()

    ActiveWorkbook.SaveAs Filename:="D:data2000excel" & Format(Date, "mmddyy")

End Sub

Change the format like you want but don’t use / (and in my opinion not – and .) as separators.

Reply | Quote

You could use the VBA Format function to convert the value of NOW() to a string representing the date, e.g. fileName = Format(Now(), “MM-DD-YY”). You can format the date as required and include the time if needed to avoid overwriting :

    Sub SaveAsDate()
        Dim strFileName as String
        strFileName = "C:My Documents" & Format(Now(), "YYYY-MM-DD hhmmss")
        ActiveWorkbook.SaveAs strFileName
    End Sub

You might need to change the path reference (in red) to something more appropriate to your requirements

Reply | Quote

I was just informed that I hav not responded to the solutions to my question.
Pardon me…no REALLY, PARDON ME.
Thanks. I’ve used a combination of both responses successfully.
Thanks to both Francois & Andrew.

Reply | Quote

the following variation on the responses by Andrew and Francois will allow you to retain the original Workbook name as your working copy.

Option Explicit


Public Sub SaveCopy()
    'In the following line replace "C:Safeplace" with your required path
    ActiveWorkbook.SaveCopyAs "C:Safeplace" & ActiveWorkbook.Name _
        & Format(Date, "mmddyy")
End Sub

Reply | Quote