Seamus2626
asked on
Create File
Hi,
I have attached some code which creates a file for me below.
The code looks into a folder and finds the latest monthj and then drops the file in.
I want to amend the code so it finds the the second most recent month file
So the change i need is if i ran it today it would save the file in Month August not September
Thanks
Seamus
I have attached some code which creates a file for me below.
The code looks into a folder and finds the latest monthj and then drops the file in.
I want to amend the code so it finds the the second most recent month file
So the change i need is if i ran it today it would save the file in Month August not September
Thanks
Seamus
Sub CreateFile()
Dim strFile As String
strFile = Date_FileName("\\ukhibmdata02\rights\Asset Services MI\MONTH END\KRI PACKS\2011\", "Asset Services KRI Pack ")
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
MsgBox "File Created, close file"
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
CharDate = Format(Date - DayDiff, "mmyy") ' Get the character date formwatted how we want it
CharMonth = Format(Date - DayDiff, "mmm yyyy")
Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
Date_FileName = Result
End Function
CharMonth = Format(Date - DayDiff, "mmm yyyy")
change this to
CharMonth = Format(DateAdd("m", -1, Date - DayDiff), "mmm yyyy")
OR
CharMonth = Format(DateAdd("m", -1, Date), "mmm yyyy")
change this to
CharMonth = Format(DateAdd("m", -1, Date - DayDiff), "mmm yyyy")
OR
CharMonth = Format(DateAdd("m", -1, Date), "mmm yyyy")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, thank you!!
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
CharDate = Format(DateAdd("m", -1, Date) - DayDiff, "mmyy") ' Get the character date formwatted how we want it
CharMonth = Format(DateAdd("m", -1, Date), "mmm yyyy")
Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
Date_FileName = Result
End Function
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
CharDate = Format(DateAdd("m", -1, Date) , "mmyy") ' Get the character date formwatted how we want it
CharMonth = Format(DateAdd("m", -1, Date), "mmm yyyy")
Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
Date_FileName = Result
End Function
change this to
CharMonth = Format(Date - DayDiff-30, "mmm yyyy")