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

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

"Corey" <(E-Mail Removed)> wrote in message
news:eN7jhwv$(E-Mail Removed)...
> 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
>
>

whatever your date is, you can use replace:

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

or

dtstr = Format(Date, "yyyymmdd")

--


Gary


"Corey" <(E-Mail Removed)> wrote in message
news:eN7jhwv$(E-Mail Removed)...
>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
>

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.
--
Regards

Corey

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

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

NickHK

"Corey" <(E-Mail Removed)> wrote in message
news:eEPQn6v$(E-Mail Removed)...
> 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.
> --
> Regards
>
> 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

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" <(E-Mail Removed)> wrote in message
news:eLcxXLw$(E-Mail Removed)...
> "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
>
>

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