MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Saving File With Month in Filename

Posted by Troy on June 22, 2001 5:24 AM

I would like ot finish off my program by savingig it with a name that includes the month and year (example: Accounts200106.xls, where "Accounts" is static and the month and year area calculated. Also I would like a popup that asks if "do you want to save Accounts200106.xls?". Thanks.

Posted by Dax on June 22, 2001 6:01 AM

The following code should do something like you want: -

Sub FilenameWithDate()
Dim iAnswer As Integer, sFilename As String

sFilename = "Accounts" & Year(Now()) & Format(Month(Now()), "00") & ".xls"

iAnswer = MsgBox("Do you want to save " & sFilename & "?", vbYesNo, _
"Save File?")

If iAnswer = vbYes Then ActiveWorkbook.SaveAs sFilename

End Sub