Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA save file with previous working day date

Posted on 2016-10-19
12
Medium Priority
?
187 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
12 Comments
 
LVL 54

Assisted Solution

by:Ryan Chong
Ryan Chong earned 1000 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 52

Accepted Solution

by:
Gustav Brock earned 1000 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 36

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 52

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 36

Expert Comment

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

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 36

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 52

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 36

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 52

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

885 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