Save Date in File Name

  • Thread starter Thread starter Duncan
  • Start date Start date
D

Duncan

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
 
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
 
Brilliant, worked a treat!

Thanks very much

Sam Wilson said:
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
Click to expand...
 
Back
Top