One way that doesn't rely on the last updated date of the text file (this could
change if someone updates the file) is to just look.

Option Explicit
Sub testme()

Dim TxtFileName As String
Dim CurDate As Date
Dim TestDate As Double
Dim TestStr As String
Dim FoundIt As Boolean
Dim dCtr As Long

CurDate = Date 'today

TestDate = CurDate
FoundIt = False
dCtr = 0
Do
TestStr = ""
On Error Resume Next
TxtFileName = "c:\my documents\excel\dividend.yester." _
& Format(TestDate, "mmmddyyyy") & ".txt"
TestStr = Dir(TxtFileName)
On Error GoTo 0
If TestStr = "" Then
TestDate = TestDate - 1
dCtr = dCtr + 1
If dCtr 100 Then
'not found in 100 previous days, get out!
Exit Do
End If
Else
FoundIt = True
Exit Do
End If
Loop

If FoundIt = False Then
MsgBox "A previous file wasn't found!"
Else
MsgBox "The filename to use is: " & TxtFileName
End If
End Sub

Don Doan wrote:

hi,
i have folder that contain one new file each day. It's a text format file.
The naming format is as follow:
The folder is located at C:\dividend. And if today's date is jan 1 2008 then
the file is dividend.yester.Jan012008.txt. So the next day, the new file
would be dividend.yester.Jan022008.txt. These file only created during
weekday.

I have to import the content of these text file into excel each day. Is it
possible to create a macro in excel that would automatically locate the most
recent file and import the content automatically??

Thanks.
Don

--

Dave Peterson