Solved

VBA save file with previous working day date

Posted on 2016-10-19
12
62 Views
Last Modified: 2016-10-20
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 "
0
Comment
Question by:Benjamin Hopper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 51

Assisted Solution

by:Ryan Chong
Ryan Chong earned 250 total points
ID: 41851463
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
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41851564
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
 
LVL 33

Expert Comment

by:ste5an
ID: 41851585
The only viable way for workday calculus is a workday table. Prefilled and manually checked. Too many exceptions exist otherwise.
0
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!

 
LVL 13

Expert Comment

by:Chris Raisin
ID: 41851618
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41851628
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
 
LVL 33

Expert Comment

by:ste5an
ID: 41851636
Well, ten years have 3650 rows. Which is already enough small :)
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41851645
Yes, it is doable that way, and we all have our preferences, but it is not "the only viable way".

/gustav
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41851652
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
 

Author Closing Comment

by:Benjamin Hopper
ID: 41851658
Thanks Gustav works perfectly for what I need
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41851662
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
 
LVL 33

Expert Comment

by:ste5an
ID: 41851728
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41851736
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

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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