Concactinating file names

[selkov]

I am tying to save a # of files by the date of the file.

I am using the following script :
Range("a2").Select
ActiveCell.FormulaR1C1 = "=YEAR(_rptPubDate)"
Range("b2").Select
ActiveCell.FormulaR1C1 = "=MONTH(_rptPubDate)"
Range("c2").Select
ActiveCell.FormulaR1C1 = "=DAY(_rptPubDate)"
Range("a2").Select
Selection.NumberFormat = "0000"
Range("b2").Select
Selection.NumberFormat = "00"
Range("c2").Select
Selection.NumberFormat = "00"
Range("d2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-3],"" "",RC[-2],"" "",RC[-1],"" "",""br_Ems"")"
Fname = Range("d2")
ActiveWorkbook.SaveAs FileName:=Fname
ActiveWorkbook.Close



The problem is that it reads the date (01/05/2003)
& saves it as (2003 1 5 br_ems)
when what I Realy want is (2003 01 05 br_ems).

any suggestions?

 

[Alan Nicoll]

I assemble a file name like this:

CurrentFile = Month(BestDate) & "_" & Day(BestDate) & "_" & Year(BestDate) & ".xls"

In place of BestDate you could use Date.

HTH

 

[Alan Nicoll]

Sorry, I didn't look closely at what you wanted.

Looks like you'd want something like:

CurrentFile = Year(BestDate) &  Right(" 0" & Month(BestDate), 3) &  Right(" 0" & Day(BestDate), 3) & " br_ems"

HTH

 

[Alan Nicoll]

That's not quite it, either. Third time's the charm:

CurrentFile = Year(BestDate) &  " " & Right("0" & Month(BestDate), 2) &  " " & Right("0" & Day(BestDate), 2) & " br_ems"

Might be easier to use the Format function or something else.

Really HTH!

 

[Greg Truby]

Not tested, but think you could cut this to one line:

ActiveWorkbook.SaveAs FileName:=Format([_rptPubDate],"yyyy mm dd ""br_EMS.xls""")

 

[selkov]

Alan,
Your The Man.
Thanks