Seamus2626
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Seamus