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

x
?
Solved

Workday+1

Posted on 2011-04-21
2
Medium Priority
?
302 Views
Last Modified: 2012-05-11
Hi,

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

Open in new window

0
Comment
Question by:Seamus2626
2 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 35442011
Seamus - does this work?
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 = 6 Then     ' If a friday then we need the date as of monday, otherwise previous day
        DayDiff = 3
    Else
        If DayOfWeek = 7 Then
          DayDiff = 2         ' If a saturday then we need the date as of monday, otherwise previous day
        Else
          DayDiff = 1         ' otherwise it should be set to the previous day
        End If
    End If
   
    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

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 35442039
perfect Stephen!

Thanks
Seamus
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question