Workday+1

Hi,

I have some code that saves files as Workday-1. Can anyone amend the code so it saves as Workday+1

Thanks
Seamus
Sub CreateFile()
Dim strFile As String
strFile = Date_FileName("\\ukhibmdata02\rights\Asset Services MI\Cash Nostros\", "Asset Services Outstanding Cash Items EOD ")

If Dir(strFile) <> "" Then
   If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
      Kill strFile
      

   Else
      Exit Sub
   End If
End If
ActiveWorkbook.SaveAs Filename:= _
strFile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Call MakeValues

 Columns("AB:AC").Select
    Selection.EntireColumn.Hidden = True

MsgBox "File Created, Kill Buttons & close file"

 ActiveWorkbook.Close SaveChanges:=True


End Sub

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
    Else
        If DayOfWeek = 1 Then
          DayDiff = 2                                           ' If a Sunday then we need the date as of friday, otherwise previous day
        Else
          DayDiff = 1                                           ' otherwise it should be set to the previous day
        End If
    End If
                 ' Get the character date formwatted how we want
    CharMonth = Format(Date - DayDiff, "m mmm yyyy")
    CharDate = Format(Date - DayDiff, "dd mmm yy")
    Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
    Date_FileName = Result
End Function

Open in new window

Seamus2626Asked:
Who is Participating?
 
StephenJRCommented:
Seamus - does this work?
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 = 6 Then     ' If a friday then we need the date as of monday, otherwise previous day
        DayDiff = 3
    Else
        If DayOfWeek = 7 Then
          DayDiff = 2         ' If a saturday then we need the date as of monday, otherwise previous day
        Else
          DayDiff = 1         ' otherwise it should be set to the previous day
        End If
    End If
   
    CharMonth = Format(Date + DayDiff, "m mmm yyyy")
    CharDate = Format(Date + DayDiff, "dd mmm yy")
    Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
    Date_FileName = Result
End Function

Open in new window

0
 
Seamus2626Author Commented:
perfect Stephen!

Thanks
Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.