Code to convert date (xx/xx/xx) to xx xx xx

[Corey]

I need to use a date value in a cell as a workbook name, but have run into
problems as, although i have changed the appearance of the date to a :
31 October 2006 value, rather than a 31/10/06.
a '/' is not a valid character for a file name, and therefore error's.

But even though the cell value (31 October 2006) does not have any '/'
(Slashes) in it, when i try to use the value as a workbook name, it still
grabs the date in a 31/10/06 format.

Therefore, is there a code i can change this date to a 31 10 06, with
Day,Month,Year separated by a single space?





Regards

Corey

 

[NickHK]

Corey,
Use Format. e.g.

Dim WBName as string
WBName=Format(Range("A1").Value,"dd-MMMM-YYYY")
'Or whatever style you want the date appear in

NickHK

 

[Gary Keramidas]

whatever your date is, you can use replace:

dtstr = Replace(Date, "/", "")

or

dtstr = Format(Date, "yyyymmdd")

 

[Corey]

Thank You for the replies, but i cannot seem to get the codes to work with
this:

ActiveWorkbook.SaveAs Filename:= _
"\\Office2\my documents\TS\" & Sheet1.Range("E2").Value & ".xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


The Date Format is in the E2 cell.

 

[NickHK]

Corey,
What is the value of Sheet1.Range("E2").Value ?

There's not code showing how you are formatting the date.

NickHK

 

[Corey]

"E2" Displays 31 October 2006,
but the formula bar displays 31/10/2006.

I have the cell formatted as a date value.

Is that what you mean?
Regards

Corey

 

[NickHK]

Corey,
There is a difference between
Range("E1").Value
and
Range("E1").Text

see what you get for :
Debug.print Range("E1").Text,Range("E1").Value

NickHK

 

[Corey]

Ok, i now understand a bit more.

I changed the .value to .text and now i get the 31 October 2006 file name.

Thank You for your assistance Nick.

-
Regards

Corey