Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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

Open in new window

0
Seamus2626
Asked:
Seamus2626
  • 4
1 Solution
 
Pratima PharandeCommented:
CharMonth = Format(Date - DayDiff, "mmm yyyy")

change this to


CharMonth = Format(Date - DayDiff-30, "mmm yyyy")
0
 
Pratima PharandeCommented:
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")
0
 
Pratima PharandeCommented:
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(DateAdd("m", -1, Date), "mmm yyyy")
    Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
    Date_FileName = Result
End Function

Open in new window

0
 
Seamus2626Author Commented:
Perfect, thank you!!
0
 
Pratima PharandeCommented:
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

Open in new window

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

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now