Concactinating file names

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Alan Nicoll

Board Regular
Joined
Feb 3, 2004
Messages
71
I assemble a file name like this:
Code:
CurrentFile = Month(BestDate) & "_" & Day(BestDate) & "_" & Year(BestDate) & ".xls"

In place of BestDate you could use Date.

HTH
 

Alan Nicoll

Board Regular
Joined
Feb 3, 2004
Messages
71
:oops:

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

Looks like you'd want something like:
Code:
CurrentFile = Year(BestDate) &  Right(" 0" & Month(BestDate), 3) &  Right(" 0" & Day(BestDate), 3) & " br_ems"
HTH
 

Alan Nicoll

Board Regular
Joined
Feb 3, 2004
Messages
71
:oops:

That's not quite it, either. Third time's the charm:
Code:
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Not tested, but think you could cut this to one line:

ActiveWorkbook.SaveAs FileName:=Format([_rptPubDate],"yyyy mm dd ""br_EMS.xls""")
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,564
Messages
5,765,129
Members
425,262
Latest member
sabry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top