VBA save file with previous working day date

Hi

I'm trying to automate a file download that saves with previous working day

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim saveFolder As String
    Dim dateformat As String
   
 
    dateformat = Format(Now() - IIf(Format(Now() - 1, "ddd") = "Sun", -3, -1), "dd-mmm-yy")
    saveFolder = "U:\uploads\Tri\performance"
   
   
        For Each objAtt In itm.Attachments
    If InStr(objAtt.DisplayName, ".xlsx") Then
        objAtt.SaveAsFile saveFolder & "\" & "Tri Performance File V2.xlsm" & dateformat & ".xlsm" & ".xlsx"
        Set objAtt = Nothing
    End If
   
    Next
End Sub

can you advise as to where I am going wrong?
file name should come out like this -" Tri Performance File V2.xlsm_20-Oct-16.xlsm.xlsx "
Benjamin HopperAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
First, never use Format this way to determine a weekday as weekday names are localized.
Second, you only take Sunday in account.

This expression will return the previous workday (Monday to Friday) of a date (ignoring public holidays, that's another story):
    Dim dateformat As String
    Dim datevalue As Date

    datevalue  = DateAdd("d", IIf(Weekday(Date, vbTuesday) <= 5, 0, 5 - Weekday(Date, vbTuesday )) - 1, Date)
    dateformat = Format(datevalue, "dd-mmm-yy")

Open in new window

/gustav
0
 
Ryan ChongConnect With a Mentor Commented:
try this instead:

dateformat = Format(Now() - IIf(Format(Now() - 1, "ddd") = "Sun", 3, 1), "dd-mmm-yy")

Open in new window

since you have a minus sign in the equation.
0
 
ste5anSenior DeveloperCommented:
The only viable way for workday calculus is a workday table. Prefilled and manually checked. Too many exceptions exist otherwise.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chris Raisin(Retired Analyst/Programmer)Commented:
In which country do you reside? Also, will the PC be online when you are running your program
(not that it matters really since historical data of holidays can be saved , with the requirement only that your PC must be online at least once per calendar year when running the program).

By the way, now that I come to think of it, are you meaning previous WORK day or are you meaning  last WEEKDAYwhch is normally a workday?
e.g. If you save the file on a Monday, do you want a "Friday" date as [part of the file name (even if the Friday was a public holiday locally?)

Is the date you want saved actually contained in the data of the file you are saving? (That would be the best solution)

Please advise. I will see if I can come up with code that will save your file with the required date as part of it's name.

Interesting challenge! :-)
0
 
Gustav BrockCIOCommented:
The only viable way for workday calculus is a workday table

Oh no. You can do with the much smaller table - on holidays.

/gustav
0
 
ste5anSenior DeveloperCommented:
Well, ten years have 3650 rows. Which is already enough small :)
0
 
Gustav BrockCIOCommented:
Yes, it is doable that way, and we all have our preferences, but it is not "the only viable way".

/gustav
0
 
ste5anSenior DeveloperCommented:
Well, I can only speak for the things I know. Here in Germany some businesses have workdays which are holidays. And some have non-workdays on normal week days. So a holiday table won't work.
0
 
Benjamin HopperAuthor Commented:
Thanks Gustav works perfectly for what I need
0
 
Gustav BrockCIOCommented:
But that's a different matter. That's about when people (employees) work. Everywhere some people work on weekends and have off-days during the week. But salary is probably different between working on a workday or public holidays.
Also, people are working at shifts in many businesses, but not all of them.

I believe we can safely ignore all this for a file download.

/gustav
0
 
ste5anSenior DeveloperCommented:
I strongly disagree.

Logically: holidays are not the binary complement of workdays.

Semantically: Someone, as least I do, will see a difference and expect a different result. E.g finding a file for a day he/she was unaware of or he/she is looking for file which does not exists. Cause we're talking about an automated process.
0
 
Gustav BrockCIOCommented:
No you don't disagree but you are mixing two different things: Official and individual workdays.
Official workdays is the complement of public holidays and weekends.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.