如何在VBA中打开文件时排除周末？

everyday one file will create with date as previous working day. Example, (05/16/2017, Tuesday) file will create with date as 05/15/2017, Monday. whereas on 05/15/2017, Monday file will create with date as 05/12/2017.

每天一个文件将创建日期为上一个工作日。示例,(2017年5月16日,星期二)文件将创建日期为星期一05/15/2017。而在2017年5月15日,星期一文件将创建日期为05/12/2017。

I am trying to open file using VBA with the below code, file name Format="C:\users\Duke\report'05/15/2017'.XLS"

我正在尝试使用以下代码的VBA打开文件,文件名格式=“C:\ users \ Duke \ report'05 / 15 / 2017'.XLS”

sub OpenFile()    
  Const fpath As String = "C:\users\Duke\Report"  
  Dim fname As String

fname = Format(Date - (Weekday((Date),Vbmonday) - 1), "yyyy-mm-dd")  
fname = "'" & fname & "'" & ".XLS"

  Dim path As String
path = fpath & fname

end ()

But it is not working, Please suggest.

但它没有用,请建议。

1 个解决方案

#1

VBA does not have a built in method to handle workdays. But the following might compute the desired date, if your code only runs M-F and SS are weekends:

VBA没有内置方法来处理工作日。但是如果您的代码仅运行M-F而SS是周末,则以下可能会计算所需的日期:

  Dim X As Long
  X = IIf(Weekday(Date) = vbMonday, 3, 1)
fname = Format(Date - X, "yyyy-mm-dd")

If your code might also run on the weekends, then you will have to define what you want to happen, in that event

如果您的代码也可能在周末运行,那么您必须在该事件中定义您想要发生的事情

#1