macro that identify the newest file in a folder and open it.
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
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
|