date in filename

[acpollack]

I'm recording a macro to take some columns from one file and put them in another. I want the new file to include in its filename the current date, so it doesn't overwrite the equivalent file from the previous day.

Is there an expression in VB to put the current date in the filename? Is there another way to do this?

[Mark O'Brien]

I don't know what syntax you're using to save the new workbook, but here is a way to add the date to the current workbook name:

ThisWorkbook.SaveAs Filename:=ThisWorkbook.Name & " " & Format(Now, "mm/dd/yyyy")

HTH

[acpollack]

thanks, I'll give it a shot!

[Al Chara]

On 2002-05-16 10:02, Mark O'Brien wrote:
I don't know what syntax you're using to save the new workbook, but here is a way to add the date to the current workbook name:

ThisWorkbook.SaveAs Filename:=ThisWorkbook.Name & " " & Format(Now, "mm/dd/yyyy")

HTH

Mark,

Shouldn't he use,

ThisWorkbook.SaveAs Filename:=ThisWorkbook.Name & " " & Format(Now, "mm_dd_yyyy")

because the slash marks will create an error.

[Mark O'Brien]

Al,

Probably should.
Good catch. Grrrr!

(I should have tested that first)

[Joe Was]

The code go's in "ThisWorkbook." Note: you will need to hardcode your file name with a "-" at the end below!

This code will, each time you open the file, save the file with the date at the end of the file name. You can also change the Sub to run on an Event or change the Sub name and run it manually. Hope this helps. JSW

Private Sub Workbook_Open()
Dim MyDate
Dim MyMonth
MyDate = Date
MyMonth = Month(MyDate)
ThisWorkbook.SaveAs Filename:="YourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date)
End Sub

[beaj]

Is there a way to do this with a cell value?
instead of the date

[AJ]

Hi beaj,

Yes you can, i.e.

ThisWorkbook.SaveAs Filename:=ThisWorkbook.Name & Range("A1").Value

Rgds
AJ