Hi I have the code below, this works fine apart from Range B6 is in the
format DD/MM/YY, I need this to change as you can't save a file with "/" in
the title.

I dont want to change the format of the Cell as Many users will be accessing
this and will all enter the date differently.

Any ideas how to add it in to my code to change the date automatically.

Cheers
Duncan

Dim WB As Workbook
Dim SH As Worksheet
Const sPath As String = "C:\Duncan\"
Set WB = ActiveWorkbook

WB.SaveAs Filename:=sPath & Range("A18").Value & " " & Range("D9").Value
& " " & Range("B6").Value, FileFormat:=xlWorkbookNormal
MsgBox "Thank you, Your Memo has been sent to Payroll & a Copy of has
been saved In: T:\Permgmt\GENTECH\GENERAL\Shared Service\Memos\ PRESS OK
TO RETURN TO THE MEMO GUIDE", vbInformation, "HEAD OFFICE - Payroll Documents"
ActiveWindow.Close

Change

Range("B6").Value

to

Replace(Range("B6").Value, "/" , "." )

You don't have to use "." obviously, you can have any valid symbol you want.

Sam

"Duncan" wrote:

Hi I have the code below, this works fine apart from Range B6 is in the
format DD/MM/YY, I need this to change as you can't save a file with "/" in
the title.

I dont want to change the format of the Cell as Many users will be accessing
this and will all enter the date differently.

Any ideas how to add it in to my code to change the date automatically.

Cheers
Duncan

Dim WB As Workbook
Dim SH As Worksheet
Const sPath As String = "C:\Duncan\"
Set WB = ActiveWorkbook

WB.SaveAs Filename:=sPath & Range("A18").Value & " " & Range("D9").Value
& " " & Range("B6").Value, FileFormat:=xlWorkbookNormal
MsgBox "Thank you, Your Memo has been sent to Payroll & a Copy of has
been saved In: T:\Permgmt\GENTECH\GENERAL\Shared Service\Memos\ PRESS OK
TO RETURN TO THE MEMO GUIDE", vbInformation, "HEAD OFFICE - Payroll Documents"
ActiveWindow.Close

Use the Format() function so that you can adjust that to a fixed format which
you would like to..

Msgbox Format(Range("B6").Value,"mmddyyyy")
OR
Msgbox Format(Range("B6").Value,"mmmddyy")

If this post helps click Yes
---------------
Jacob Skaria


"Duncan" wrote:

Hi I have the code below, this works fine apart from Range B6 is in the
format DD/MM/YY, I need this to change as you can't save a file with "/" in
the title.

I dont want to change the format of the Cell as Many users will be accessing
this and will all enter the date differently.

Any ideas how to add it in to my code to change the date automatically.

Cheers
Duncan

Dim WB As Workbook
Dim SH As Worksheet
Const sPath As String = "C:\Duncan\"
Set WB = ActiveWorkbook

WB.SaveAs Filename:=sPath & Range("A18").Value & " " & Range("D9").Value
& " " & Range("B6").Value, FileFormat:=xlWorkbookNormal
MsgBox "Thank you, Your Memo has been sent to Payroll & a Copy of has
been saved In: T:\Permgmt\GENTECH\GENERAL\Shared Service\Memos\ PRESS OK
TO RETURN TO THE MEMO GUIDE", vbInformation, "HEAD OFFICE - Payroll Documents"
ActiveWindow.Close

Brilliant, worked a treat!

Thanks very much

"Sam Wilson" wrote:

Change

Range("B6").Value

to

Replace(Range("B6").Value, "/" , "." )

You don't have to use "." obviously, you can have any valid symbol you want.

Sam

"Duncan" wrote:

Hi I have the code below, this works fine apart from Range B6 is in the
format DD/MM/YY, I need this to change as you can't save a file with "/" in
the title.

I dont want to change the format of the Cell as Many users will be accessing
this and will all enter the date differently.

Any ideas how to add it in to my code to change the date automatically.

Cheers
Duncan

Dim WB As Workbook
Dim SH As Worksheet
Const sPath As String = "C:\Duncan\"
Set WB = ActiveWorkbook

WB.SaveAs Filename:=sPath & Range("A18").Value & " " & Range("D9").Value
& " " & Range("B6").Value, FileFormat:=xlWorkbookNormal
MsgBox "Thank you, Your Memo has been sent to Payroll & a Copy of has
been saved In: T:\Permgmt\GENTECH\GENERAL\Shared Service\Memos\ PRESS OK
TO RETURN TO THE MEMO GUIDE", vbInformation, "HEAD OFFICE - Payroll Documents"
ActiveWindow.Close