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/