MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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

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()

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, "######")
Filedate_LastMod = 0 '"Error"
End If
End Function