I have attached some code which saves a file down. I have two changes i would like to make.
(1) I would like the file to be saved down with todays date, i.e. no day difference.
(2) The code currently looks for a dated folder when saving down, so i get the attached error message. I need it only to look for the path
and then save with todays date
Dim strFile As String
strFile = Date_FileName("\\Ukhibmdat
sk Manager\", "Task Manager ")
If Dir(strFile) <> "" Then
If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
ActiveWorkbook.SaveAs Filename:= _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
MsgBox "File Created, close file"
Function Date_FileName(pPath As String, pFilePrefix As String) As String
Dim DayOfWeek As Integer, DayDiff As Integer, CharDate As String, MonthNo As Integer, CharYear As String, CharMonth As String
Dim Result As String
DayOfWeek = Weekday(Date) ' Sunday is 1, Monday is 2, Tuesday is 3 etc
If DayOfWeek = 2 Then ' If a monday then we need the date as of friday, otherwise previous day
DayDiff = 3
If DayOfWeek = 1 Then
DayDiff = 2 ' If a Sunday then we need the date as of friday, otherwise previous day
DayDiff = 1 ' otherwise it should be set to the previous day
CharDate = Format(Date - DayDiff, "DD MMM YY") ' Get the character date formwatted how we want it
CharMonth = Format(Date - DayDiff, "MM MMM YY")
Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
Date_FileName = Result