Rename File As Date Name

I need someones' expertise!

I want to save as, or rename an Excel file as part of a visual basic macro within Excel, to a filename containing the current date or for example date -1.

I have Excel files that update daily from a database as part of a scheduled job, and at the weekend I need these to be archived separately rather than overwriting the previous days' information.

If anyone can help, I'd be grateful...

You need to know:
- what statement in VBA could perform the saveAs function. I assume you know this, then
- Because the filename is a string and it is one of the agruments for the saveAs method of an Excel workbook, all you need in your program is to work out the string based on a data of the type date.

The following is an example of creating a string with date information.

private sub makeString()
' to generate a string = "AA20010813" (today is 13/8) you may do this
Dim fileNameWithDate$
Dim tmpS$
tmpS= format(year(date()),"0000") + format(month(date()),"00") + format(day(date()),"00")
fileNameWithDate = "AA" + tmpS
End sub