Business Day code VBA 3

[Jimmylaki]

Hello,

I need to modify my code which I use for opening a file for today -1 however, I need the file to select the last business day excluding WEEKENDS. I am using this as a sub
'TO OPEN THE FILE TO FILTER AND COPY

Dim yestdate As String
yestdate = Format(Date - 1, "ddmm") & ".xls"
Workbooks.Open Filename:="I:\Report\amendments" & yestdate

if someone could please help for a more useful code.

thanks JL

 

[SkipVought]

Hi,

Use a loop, subtracting days until you have a date that works. Of course, trap the errors.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joelflorendo]

check this out:

http://www.mvps.org/access/datetime/date0012.htm

You could use the dhPreviousWorkdayA function given.

 

[GKChesterton]

SkipVaught and joelflorendo have provided fine solutions. Which to use depends on whether you want the last file regardless of date, or want a file only if it is found with the last business-day date.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​