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
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
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, "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