Append Date

y2k · Mar 5th, 2002, 08:30 AM

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 · Mar 5th, 2002, 08:35 AM

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 · Mar 5th, 2002, 08:39 AM

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 · Mar 5th, 2002, 09:02 AM

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 · Mar 5th, 2002, 09:15 AM

"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 · Mar 5th, 2002, 09:17 AM

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 · Mar 5th, 2002, 09:17 AM

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 · Mar 5th, 2002, 09:26 AM

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 · Mar 5th, 2002, 09:27 AM

It works perfect!! Thank you both very much

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 08:30 AM	#1
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	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 ]

Mar 5th, 2002, 08:35 AM	#2
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	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 ]

Mar 5th, 2002, 08:39 AM	#3
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	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"

Mar 5th, 2002, 09:02 AM	#4
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	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

Mar 5th, 2002, 09:15 AM	#5
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	"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 ]

Mar 5th, 2002, 09:17 AM	#6
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	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

Mar 5th, 2002, 09:26 AM	#8
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	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

Mar 5th, 2002, 09:27 AM	#9
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	It works perfect!! Thank you both very much