Check if a directory exists and if it doesn t...

G

Grek

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
 
G

Greg Koppel

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

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

HTH, Greg
 
G

Grek

Hi,

Thank you for your response.

I ve just adapted it and tested it but it doesn t work. There is n
error message but the code runs without stopping...

Here is my code, could you please have a look at this and tell m
what's wrong ?

I also read in a book that a function FolderExists exists. Maybe it i
more simple to use....

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

mydate = Now() - 1


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

Do Until mydir <> ""
mydate = mydate - 1
Loop

Workbooks.Open Filename:= _
"c:\Greg\" & Format(mydate, "yyyymmdd") & "\" & "test.xls"

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

So, it's supposed to try to locate a directory called day-1 or day-2
day-3,... until the folder exists...

Thank for your help

Gre
 
R

Rob van Gelder

I have a FolderExists sample on my website under "Test if directory exists".
 
G

Grek

Hi,

Thank you for you response. I will check the sample on your website bu
I would really like to know why my code doesn t work...

Does anyone has an idea what s wrong in that code ?

Thanks,

Greg

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

mydate = Now() - 1


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

Do Until mydir <> ""
mydate = mydate - 1
Loop

Workbooks.Open Filename:= _
"c:\Greg\" & Format(mydate, "yyyymmdd") & "\" & "test.xls"

*******************************************
 
S

Steve Garman

Nothing is changing mydir in your loop, so it never becomes "" and keeps
looping.

Do Until mydir <> ""
mydate = mydate - 1
Loop
 
S

Steve Garman

Oops! I meant to suggest this

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

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

Steve said:
Nothing is changing mydir in your loop, so it never becomes "" and keeps
looping.

Do Until mydir <> ""
mydate = mydate - 1
Loop
Click to expand...
 
G

Grek

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"

******************
 
S

Steve Garman

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

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

Steve Garman

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 said:
Sorry, didn't look at your code as a whole

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

Grek

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
 
Top