VBA-如果前一天是银行假期，请从前一个工作日打开文件 [英] VBA - If Previous Day is a Bank Holiday, Open File From Previous Working Day

问题描述

我正在努力解决这一问题.基本上，我目前拥有的是打开前一个工作日文件的代码.

I am struggling with working this one out. Basically, what I currently have is code to open the previous working days file.

x = Weekday(Date, vbSunday)
    Select Case x
        Case 1
            x = 2
        Case 2
            x = 3
        Case Else
            x = 1

    End Select

    Workbooks.Open Filename:= _
    "filepath" & Format(Date - x, "yymmdd") & " - filename.xlsx"

很显然，以上内容并未考虑银行/公共假期.如何将其构建到我的代码中，例如:

Obviously the above doesn't take into consideration Bank/Public Holidays. How can I build this into my code, so for example:

2018年3月29日星期四-工作日

Thursday 29/03/2018 - Working day

2018年3月30日星期五-耶稣受难节(银行假期)

Friday 30/03/2018 - Good Friday (Bank Holiday)

星期一02/04/2018-复活节星期一(银行假日)

Monday 02/04/2018 - Easter Monday (Bank Holiday)

2018年3月4日星期二-工作日

Tuesday 03/04/2018 - Working day

当我星期二进入并运行宏时，我希望它获取上一个工作日文件并使用该文件(29/29/03星期四).用我当前的代码，它将不会被提取，它将寻找星期一文件(显然不存在).

When I come in on Tuesday and run my macro I want it to pick up the last working days file and use that (Thursday 29/03). With my current code that wouldn't be picked up and it would be looking for Mondays file (which obviously doesn't exist).

我希望这是有道理的！

谢谢，杰森

推荐答案

您可以使用

• WorksheetFunction.WorkDay方法或
• WorksheetFunction.WorkDay_Intl方法
  (可让您选择一周中的哪几天是周末)

因此，通过…获得上一个工作日.

So get the last previous workday with …

Dim LastPreviousWorkday As Date
LastPreviousWorkday = Application.WorksheetFunction.WorkDay(Date(), -1)

例如输出格式

Format$(LastPreviousWorkday, "yymmdd")

您可以告诉 WorkDay 函数哪个日期(除周末外)应被视为假期，例如，通过将数组或范围作为第三个参数.

You can tell the WorkDay function which dates (additionally to weekends) should be considered as holidays, eg by giving an array or range as third argument.

Dim BankHolidays As Variant
BankHolidays = Array(#3/26/2018#, #3/23/2018#) 'array of bank holidays, or a range in a 
                                               'sheet where the dates of bank holidays
                                               'are saved in.

Dim LastPreviousWorkday As Date
LastPreviousWorkday = Application.WorksheetFunction.WorkDay(Date, -1, BankHolidays)

或者如果您想使用带有假期日期的工作表

or if you want to use a worksheet with holiday dates

Application.WorksheetFunction.WorkDay(Date, -1, Worksheets("MyHolidays").Range("A:A"))
 'considers all dates in column A of sheet MyHolidays as non-workdays

这篇关于VBA-如果前一天是银行假期，请从前一个工作日打开文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！