FileCopy with Todays Date

[sunrunner4kr]

Hi,

I want at a click of a button, to copy a file and rename it with Todays date.

C:\FILES\ALLOC.csv
C:\FILES\ALLOC191109.csv

I've got the FILECOPY code working:

Private Sub rename_Click()

FileCopy "C:\FILES\ALLOC.CSV", "C:\FILES\ALLOC191109.csv"

End Sub

but I've tried adding the date, and I'm not sure where I'm going wrong.

Private Sub rename_Click()

Dim NewName As String

NewName = "C:\FILES\ALLOC" & Date & ".CSV"
FileCopy "C:\FILES\ALLOC.CSV", NewName

End Sub

Any help would be greatly appreciated. Thanks.

 

[MarkK]

You haven't mentioned what error you're getting or at what line of code!

My guess is that the value of NewName just after this line executes ...

NewName = "C:\FILES\ALLOC" & Date & ".CSV"

... is an illegal filename.

 

[sunrunner4kr]

Sorry...

...error highlighted on:

FileCopy "C:\FILES\ALLOC.CSV", NewName

Runtime error 76: Path not found.

wasn't sure if the FileCopy function allowed for a variable string.

Cheers

 

[MarkK]

So did you solve this? The date function will return a value like 11/19/09 and those slash characters "/" are illegal in a filename.
You could, among other things, use the Replace() function ...

NewName = "C:\SomePath\SomeFile_" & Replace(Date, "/", "-") & ".csv"
debug.print NewName

You should get ....

C:\SomePath\SomeFile_11-19-09.csv

 

[sunrunner4kr]

That worked a treat!

THANK YOU!!

 

[DCrake]

To improve on your code you would be better converting the date element of the file name to

Format(Date(),"yymmdd")

That way when you read the files in your folder they would appear in the correct order, when sorted.

David

 

[namliam]

Format(Date(),"yymmdd")

Y2k, or Y2.1k

Perhaps use yyyy instead of yy?

 

[smig]

NewName = "C:\SomePath\SomeFile_" & Format(Date(),"yyyymmdd")
& ".csv"

should work.