Hi, I was wondering if someone could show me how, give a directory (n
need for sub directories or hidden files or system files, etc) to ope
the last modified excel file given a partial string (the beginning par
of the filename) using VBA, preferably using the "Dir" function

--
farfu
-----------------------------------------------------------------------
farful's Profile: http://www.excelforum.com/member.php...fo&userid=3617
View this thread: http://www.excelforum.com/showthread.php?threadid=56336

Hi farful,

the code below opens the last modified excel file with a filename like
"ABC...." in folder "C:\Temp\". Instead of the "Dir" function it uses
the FileSystemObject. You may proof me wrong here, but I don't think
it's even possible to retrieve the date and time of the last
modification of a given file using the "Dir" function. To access the
property "DateLastModified" of a given file you need that file as an
object, whereas the "Dir" function returns path and name of files as a
string instead.

Regards,
Ingolf

Sub filesearch()

'--------------------------------
Dim fso As Object
Dim fo As Object
Dim f As Object
Dim LastMod As Date
Dim FilePathName As String
'--------------------------------


Set fso = CreateObject("Scripting.FileSystemObject")
Set fo = fso.GetFolder("C:\Temp\")
For Each f In fo.Files
If Left(f.Name, 3) = "ABC" Then
If f.datelastmodified LastMod Then
LastMod = f.datelastmodified
FilePathName = f.Path
End If
End If
Next 'f

Workbooks.Open Filename:=FilePathName

End Sub



farful schrieb:

Hi, I was wondering if someone could show me how, give a directory (no
need for sub directories or hidden files or system files, etc) to open
the last modified excel file given a partial string (the beginning part
of the filename) using VBA, preferably using the "Dir" function.


--
farful
------------------------------------------------------------------------
farful's Profile: http://www.excelforum.com/member.php...o&userid=36172
View this thread: http://www.excelforum.com/showthread...hreadid=563360

How about this:

Private Sub CommandButton1_Click()
Debug.Print GetOldestModifiedFile("C:")
End Sub

Public Function GetOldestModifiedFile(argFolderToSearch As String, _
Optional argPattern As String =
"*.xls") _
As String
Dim FileName As String
Dim FileDate As Date
Dim OldestDate As Date
Dim OldestFile As String

If Right(argFolderToSearch, 1) < "\" Then argFolderToSearch =
argFolderToSearch & "\"
OldestFile = ""
OldestDate = 50000

FileName = Dir(argFolderToSearch & argPattern, vbNormal)
Do Until FileName = ""
FileDate = FileDateTime(argFolderToSearch & FileName)
If FileDate < OldestDate Then
OldestDate = FileDate
OldestFile = FileName
End If
FileName = Dir()
Loop
GetOldestModifiedFile = OldestFile

End Function

NickHK

"farful" wrote in
message ...

Hi, I was wondering if someone could show me how, give a directory (no
need for sub directories or hidden files or system files, etc) to open
the last modified excel file given a partial string (the beginning part
of the filename) using VBA, preferably using the "Dir" function.


--
farful
------------------------------------------------------------------------
farful's Profile:
http://www.excelforum.com/member.php...o&userid=36172
View this thread: http://www.excelforum.com/showthread...hreadid=563360