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

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

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


"davethewelder" wrote:

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, 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" wrote:

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

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


"davethewelder" wrote:

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

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

"davethewelder" wrote:

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" wrote:

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

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


"davethewelder" wrote:

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, many thanks it works a treat. Amazing how one little missing bit can
stump you for ages.

Cheers

Davie

"Joel" wrote:


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

"davethewelder" wrote:

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" wrote:

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

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


"davethewelder" wrote:

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

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" wrote:

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

Cheers

Davie

"Joel" wrote:


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

"davethewelder" wrote:

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" wrote:

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

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


"davethewelder" wrote:

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

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

Davie

"Joel" wrote:

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" wrote:

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

Cheers

Davie

"Joel" wrote:


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

"davethewelder" wrote:

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" wrote:

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

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


"davethewelder" wrote:

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

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

"davethewelder" wrote:

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

Davie

"Joel" wrote:

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" wrote:

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

Cheers

Davie

"Joel" wrote:


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

"davethewelder" wrote:

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" wrote:

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

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


"davethewelder" wrote:

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

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



"Joel" wrote:

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

"davethewelder" wrote:

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

Davie

"Joel" wrote:

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" wrote:

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

Cheers

Davie

"Joel" wrote:


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

"davethewelder" wrote:

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" wrote:

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

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


"davethewelder" wrote:

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