Append Date

[y2k]

Does anybody know how to use VBA to save a file with with date appended onto the end? Eg save "file1" as "file1 01-01-02"? If necessary the date can be entered into one of the fields in the spreadsheet.
This message was edited by y2k on 2002-03-05 07:30

 

[Steve Hartman]

ActiveWorkbook.SaveAs Filename:="file1 " & Date & ".xls"

Will save the file with todays date

ActiveWorkbook.SaveAs Filename:="file1 " & Range(Date) & ".xls"

Will save the file with the date stored in the range "Date" on the worksheet
This message was edited by Steve Hartman on 2002-03-05 07:38

 

[AJ]

If the date is in Cell A1 for example,
then just get that in to a variable...

myDate = Range("A1").Value

And then save your file:

ActiveWorkbook.SaveAs Filename:="File 1 " & mydate & ".xls"

 

[y2k]

Help... it's not working! What am I doing wrong? When I run the macro, I get an error message saying it can't access the file "C:file1 05

 

[Steve Hartman]

"C:\file1 05\03"

Are the double slashes actually in your code or is it just the way it shows up on this board? If you do have double slashes there try it with single slashes.
This message was edited by Steve Hartman on 2002-03-05 08:15

 

[AJ]

I think it's the fact the the & Date & produces a date with a slash as the day/month delimiter and you can't have slashes in your filenames.

The following worked for me. Write the date to a cell, pick up it's components and then save (substitute in your own path)...

--
Range("A1").Formula = Now()
Range("B1").FormulaR1C1 = "=DAY(RC[-1])"
Range("C1").FormulaR1C1 = "=MONTH(RC[-2])"
Range("D1").FormulaR1C1 = "=YEAR(RC[-3])"

MyDay = Range("B1").Value
MyMonth = Range("C1").Value
MyYear = Range("D1").Value

ChDir ("C:temp")

ActiveWorkbook.SaveAs Filename:="All_claims " & MyDay & "-" & MyMonth & "-" & MyYear & ".xls"

--
This called my file
"All_claims 5-2-2002.xls"
It ain't elegant, but it works.

Rgds
AJ

 

[y2k]

no... the double slashes are the way the board is showing it. I've only gone single slashes, excep at the beginning of the UNC, but that's supposed to have 2 slashes

 

[Steve Hartman]

AJ is right, the slash in the filename is the problem.

A slightly neater way would be:

ActiveWorkbook.SaveAs Filename:="All_claims " & Month(Date) & Day(Date) & Year(Date) & ".xls"

to save as todays date

 

[y2k]

It works perfect!! Thank you both very much