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

x
?
Solved

VBA save file with previous working day date

Posted on 2016-10-19
12
Medium Priority
?
250 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 55

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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
A few solutions to a problem some of us have been having when trying to add Hostgator email accounts to Outlook 2016 (will probably work with Outlook 2013 as well).
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

580 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