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
Solved

VBA save file with previous working day date

Posted on 2016-10-19
12
54 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 50

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 49

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 49

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 49

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 49

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 49

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to line 23 53
Hybrid Exchange dual mailbox issue 3 32
MS Access VBA Code to Include Library References 4 49
Schedule Outlook Calendar 5 30
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

840 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