Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.