open a file automatically which was created yesterday

[davethewelder]

Hi, i have a macro which opens a file which is created automatically with a
date in the filename. I can add the date to the filename but I want to open
the file in a series of macros to be run every morning. I have tried using
some examples from the disscussion board but no success yet.

I want the filename updated with yesterday's date.

The constant name of the file is Report15Probability whith the date
yyyymmdd. I am about to try the LastMofifiedYesterday property but I am
running out of options.

Sorry for not posting the code but this is due to restrictions on e-mail

Hope some one can help.

Davie

 

[Joel]

Below will work, but are you including weekends and holidays?-

basename = "Report15Probability"
yesterday = Format(Date - 1, "yyyymmdd")
NewName = basename & yesterday

 

[davethewelder]

Joel, I am not including weekends but I don't think it makes a difference if
it takes todays' date and subtracts one.

I tried the code below but it did not find the file "NewName".

ub tryopen()
basename = "Report15Probability"
yesterday = Format(Date - 1, "yyyymmdd")
NewName = basename & yesterday

'
ChDir "Z:\"
Workbooks.Open Filename:="Z:\NewName.csv"
End Sub

Have I entered this correctly?
Sometimes on this computer when you hit New or reply it does not open the
popup window to reply.

 

[Joel]

Workbooks.Open Filename:="Z:\" & NewName & ".csv"

 

[davethewelder]

Joel, many thanks it works a treat. Amazing how one little missing bit can
stump you for ages.

Cheers

Davie

 

[Joel]

What are you going to do on Monday? going back one day will not get Friday.
One solution would be to look for the latest file.

 

[davethewelder]

doh! I can see the problem. Is there a Latest property?

Davie

 

[Joel]

Everybody makes the same mistakes. There are hundreds of people who ask for
yesterday files and forget that there are weekends and holidays.

Sub tryopen()
BaseName = "Report15Probability"
PrefixLen = Len(BaseName)

Searchname = "Z:\" & BaseName & "*.csv"

LatestDate = 0
First = True
Do
If First = True Then
FName = Dir(Searchname)
First = False
Else
FName = Dir()
End If
If FName <> "" Then
NewDate = Mid(FName, PrefixLen + 1, 8)
FDate = DateSerial(Left(NewDate, 4), Mid(NewDate, 5, 2), Mid(NewDate,
7, 2))
If FDate > LatestDate Then
LatestDate = FDate
End If
End If
Loop While FName <> ""

yesterday = Format(FDate, "yyyymmdd")
NewName = BaseName & yesterday

'
ChDir "Z:\"
Workbooks.Open Filename:="Z:\NewName.csv"
End Sub

 

[Joel]

There is on e minor change you may want to include. If somebody already
saved a file with todays date and then runs the macro it will return todays
file and not yesters file. So add this change

from
If FDate > LatestDate Then

to
If (FDate > LatestDate) and _
(FDate <> Date) Then