Save Date in File Name

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
 
S

Sam Wilson

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
 
J

Jacob Skaria

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
 
D

Duncan

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top