open file by latest date/time
Posted by Keith on September 06, 2001 10:41 AM
Everyday a file is created with misc. plant data. I open the file, grab what I need, close and run my calculations and pivots. The problem is, the filenames
are generated with a name, date, and timestamp built in (Ex. C_MORE_010812_071500.CSV = 8-12-2001, 7:15:00 am) and the timestamp is NEVER the same. So I would like to open the newest file in the directory. Thank you in advance for your assistance.
Posted by Mark O'Brien on September 06, 2001 2:44 PM
Browse to the directory the files are in using Windows Explorer. On the "View" menu, make sure the option "Details" is selected. Click on the column header that says "Modified". The newest file will be put to the top of the list. Click "Modified" again and the list will be in reverse.
Posted by Keith on September 07, 2001 4:51 AM
I should have been more specific, I want to open it with a macro, but the name never had the same format.
Posted by Ivan F Moala on September 07, 2001 7:36 PM
Keith
One way to do this is........
Change as required or repost for help
'====start code======
Option Explicit
Option Base 1
Sub LoadLastesFile()
Dim F
Dim SearchDir As String 'Directory to search
Dim Ext As String 'File extension eg .csv, .xls, .xla etc
Dim x As Double 'counter
Dim FDateName() 'File data array to store dates & name
Dim LastestFileToOpen As String 'name of latest file to open
'/// change this to your Dir and Extension ///
SearchDir = "C:\Excelfiles\"
Ext = "*.xls"
'/////////////////////////////////////////////
ChDir (SearchDir)
F = Dir(SearchDir & Ext)
Application.DisplayAlerts = False
Application.ScreenUpdating = False
x = 1
Sheets.Add
With ActiveSheet
Do While Len(F) > 0
ReDim Preserve FDateName(2, x)
FDateName(1, x) = Filedate_LastMod(SearchDir & F)
FDateName(2, x) = SearchDir & F
Cells(x, 1) = FDateName(1, x)
Cells(x, 2) = FDateName(2, x)
x = x + 1
F = Dir()
Loop
If x = 1 Then MsgBox "No Files": GoTo Ex
Range("A1", Range("A1").End(xlDown).End(xlToRight).Address).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
LastestFileToOpen = Range("B1")
Ex: ActiveSheet.Delete
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
If Not (x = 1) Then Workbooks.Open LastestFileToOpen
End Sub
Function Filedate_LastMod(Filename As String) As String
Dim FileDate As Double
On Error Resume Next
FileDate = FileDateTime(Filename)
If Err.Number = 0 Then
Filedate_LastMod = Format(FileDate, "######")
Else
Filedate_LastMod = 0 '"Error"
End If
End Function
Ivan
