Hi,

To open a file in a directory (C:\Export Archive\yyyymmdd\) with dat
day-1 I use the follwing code :

variablefile = Range("E1").Value

Workbooks.Open Filename:= _
"C:\Export Archive\" & Format(Now() - 1, "yyyymmdd") & "\"
"Export" & variablefile & ".xls"

(a directory is saved every day from monday to friday)

My code works pretty good, exept on monday because it looks for th
directory day-1. And as there is no directory saved with sunday's o
saturday's date, it doesn t work. Same problem with holidays.

So, I would like to improve my code to do something like "open th
directory with date day-1. If the directory does not exist, try day-2
or day-3,... until it finds the directory with the most recent date.

Could you help me to do that please ?

Maybe something like setting the date as variable and increasing it b
doing a loop until it finds a directory with the same date ?

Thank you very much in advance for you help,

Gre

--
Message posted from http://www.ExcelForum.com

Use the Dir() function. It returns "" if the file doesn't exist, so
something like

myFile = Dir(pathname...)
If myFile = "" then

HTH, Greg

"Grek " wrote in message
...
Hi,

To open a file in a directory (C:\Export Archive\yyyymmdd\) with date
day-1 I use the follwing code :

variablefile = Range("E1").Value

Workbooks.Open Filename:= _
"C:\Export Archive\" & Format(Now() - 1, "yyyymmdd") & "\" &
"Export" & variablefile & ".xls"

(a directory is saved every day from monday to friday)

My code works pretty good, exept on monday because it looks for the
directory day-1. And as there is no directory saved with sunday's or
saturday's date, it doesn t work. Same problem with holidays.

So, I would like to improve my code to do something like "open the
directory with date day-1. If the directory does not exist, try day-2,
or day-3,... until it finds the directory with the most recent date.

Could you help me to do that please ?

Maybe something like setting the date as variable and increasing it by
doing a loop until it finds a directory with the same date ?

Thank you very much in advance for you help,

Greg


---
Message posted from http://www.ExcelForum.com/

Thank you for your help Steve.

I ve updated my code but unfortunately it keeps looping.

For my test i created a workbook gregtest.xls in c:\Greg\
In this directory I have 2 others directory 20040508 and 20040506. So
the code is supposed to open the file test.xls in the director
20040506

Here is the updated code :

*******************

mydate = Now() - 1

mydir = Dir("c:\Greg\" & Format(mydate, "yyyymmdd"))

Do Until mydir < ""
mydate = mydate - 1
mydir = Dir("c:\Greg\" & Format(mydate, "yyyymmdd"))
Loop

Workbooks.Open Filename:= _
mydir & "\" & "test.xls"

******************

--
Message posted from http://www.ExcelForum.com

Sorry, didn't look at your code as a whole

Try
mydir = Dir("c:\Greg\" & Format(mydate, "yyyymmdd"), vbDirectory)
in both cases

Grek < wrote:
Thank you for your help Steve.

I ve updated my code but unfortunately it keeps looping.

For my test i created a workbook gregtest.xls in c:\Greg\
In this directory I have 2 others directory 20040508 and 20040506. So,
the code is supposed to open the file test.xls in the directory
20040506

Here is the updated code :

*******************

mydate = Now() - 1

mydir = Dir("c:\Greg\" & Format(mydate, "yyyymmdd"))

Do Until mydir < ""
mydate = mydate - 1
mydir = Dir("c:\Greg\" & Format(mydate, "yyyymmdd"))
Loop

Workbooks.Open Filename:= _
mydir & "\" & "test.xls"

*******************


---
Message posted from http://www.ExcelForum.com/

So, for the sake of completeness, this should be a version of your code
which actually works

Dim myDate As Date, myDir As String
myDate = Now() - 1

myDir = Dir("c:\Greg\" & Format(myDate, "yyyymmdd"), vbDirectory)

Do Until myDir < ""
myDate = myDate - 1
myDir = Dir("c:\Greg\" & Format(myDate, "yyyymmdd"), vbDirectory)
Loop

Workbooks.Open Filename:="c:\Greg\" & myDir & "\" & "test.xls"



Steve Garman wrote:

Sorry, didn't look at your code as a whole

Try
mydir = Dir("c:\Greg\" & Format(mydate, "yyyymmdd"), vbDirectory)
in both cases

Grek < wrote:

Thank you for your help Steve.

I ve updated my code but unfortunately it keeps looping.

For my test i created a workbook gregtest.xls in c:\Greg\
In this directory I have 2 others directory 20040508 and 20040506. So,
the code is supposed to open the file test.xls in the directory
20040506

Here is the updated code :

*******************

mydate = Now() - 1

mydir = Dir("c:\Greg\" & Format(mydate, "yyyymmdd"))

Do Until mydir < ""
mydate = mydate - 1
mydir = Dir("c:\Greg\" & Format(mydate, "yyyymmdd"))
Loop

Workbooks.Open Filename:= _
mydir & "\" & "test.xls"

*******************


---
Message posted from http://www.ExcelForum.com/

GREAT, the code works perfectly now !
Thanks a lot !

Actually, why the vbDirectory is so important in the code and why i
didn t work without that ?

Thanks again,

Gre

--
Message posted from http://www.ExcelForum.com