macro that identify the newest file in a folder and open it.

[Don Doan]

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]

Is it always the next day?

What about on Sunday and Monday?

Either way, maybe you can modify this:

Option Explicit
Sub testme()

Dim PrevDayTxtFileName As String
Dim PrevDay As Date
Dim CurDate As Date

CurDate = Date 'change the date here for testing

Select Case Weekday(CurDate, vbMonday)
Case Is = 2, 3, 4, 5, 6 'Tues - Sat
PrevDay = CurDate - 1 'Mon - Fri
Case Is = 7 'Sun
PrevDay = CurDate - 2 'Fri
Case Is = 1 'Mon
PrevDay = CurDate - 3 'Fri
End Select

PrevDayTxtFileName _
= "dividend.yester." & Format(PrevDay, "mmmddyyyy") & ".txt"

MsgBox PrevDayTxtFileName

End Sub

 

[Anand.V.V.N]

Hello, you can do this using VB script, you can ge tthe date and time stamp
of the file, and check when the file was created.
Hope this is helpful
Anand.V.V.N

 

[Joel]

Try this code. I don't know how you want to read the text file so I did not
include code to do the importing, just the code to find the file with the the
latest filename. I also noticed you had an extra 0 between the three
character Month name and the date. Is this correct?



Sub getlatest()

Folder = "c:\dividend\"
Prefix = "dividend.yester."

First = True
Do
If First = True Then
Filename = Dir(Folder & "*.txt")
Else
Filename = Dir()
End If
If Filename <> "" Then
If Filename = Left(Filename, Len(Prefix)) Then
FileDateString = Mid(Filename, Len(Prefix) + 1)
'remove .txt
FileDateString = Left(FileDateString, _
Len(FileDateString) - 4)
'file date has a three character month followed by an extra 0
FileDate = DateValue(Left(FileDateString, 3) & " " & _
Mid(FileDateString, 5, 2) & " " & _
Mid(FileDateString, 7, 4))
If First = True Then
LatestFile = Filename
LatestDate = FileDate
Else
If FileDate > LatestDate Then
LatestFile = Filename
LatestDate = FileDate
End If
End If
End If
End If
First = False
Loop While Filename <> ""
' open file named = Folder & LatestFile
'
' Enter code to import data file here
'
End Sub

 

[SeanC UK]

Hi Don,

One way you could do it is to look at the newest file created in the folder,
this could involve creating a File System object and examining the folder.

However, if you already know much of the filename, and it is only the date
portion that is changing, then here's an easier way to do it:

Dim strDay As String
Dim strMon As String
Dim strYear As String
Dim dteDate As Date
Dim strFilename As String
dteDate = Date
strDay = Format(dteDate, "dd")
strMon = Format(dteDate, "mmm")
strYear = Format(dteDate, "yyyy")
strFilename = "dividend.yester." & strMon & strDay & strYear & ".txt"

This will give you a filename representing today's date. Should you wish to
look at the day before then change the dteDate line to:
dteDate = DateAdd("d", -1, Date)
etc

I hope this helps,

Sean.

 

[Dave Peterson]

First, I misread the question. I thought that file would be based on the
previous workday's date.

 

[Dave Peterson]

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]

THANK YOU ALL FOR YOUR HELP.
I will test these codes out.

Again, thank you so much.