code to pick out the oldes file from a folder

  • Thread starter Thread starter Michel Peeters
  • Start date Start date
M

Michel Peeters

In Access 2002 I use the following code to open the file dialog box which
allows the user to pick out a folder.

With Application.FileDialog(4)
.AllowMultiSelect = True
.InitialFileName = "c:\foldername"
If .Show = True Then
Me![txtPath] = Left$(.SelectedItems(1), InStrRev(.SelectedItems(1),
"\")) & _
Right$(.SelectedItems(1), Len(.SelectedItems(1)) -
InStrRev(.SelectedItems(1), "\"))
End If
end with

This works ok.
Because the foldername in txtPath nearly never changes in my application, I
would like to create a command button which automaticly makes a string with
the name of the most recent file in the txtPath-folder , without opening the
filebox dialog.
Any idea how to start or where I could find it?

Michel
 
Something like this, which works but has not been thoroughly tested:


Public Function LastModifiedFileInFolder(ByVal Folder As String, _
ByVal Wildcard As String)

'Returns the name of the most recently modified file in Folder
'that matches Wildcard. If the timestamps are the same (to the
'nearest second, returns the first file found. If Folder does
'not exist, or no file matches Wildcard, returns an empty
'string.

Dim FileName As String
Dim NewestDate As Date
Dim NewestName As String
Dim FileDate As Date
Const PATH_SEPARATOR = "\"

If Right(Folder, 1) <> PATH_SEPARATOR Then
Folder = Folder & PATH_SEPARATOR
End If
NewestDate = #1/1/1900#
FileName = Dir(Folder & Wildcard)
Do While Len(FileName) > 0
FileDate = CDate(FileDateTime(Folder & FileName))
If FileDate > NewestDate Then
NewestDate = FileDate
NewestName = FileName
End If
FileName = Dir()
Loop
LastModifiedFileInFolder = NewestName
End Function


In Access 2002 I use the following code to open the file dialog box which
allows the user to pick out a folder.

With Application.FileDialog(4)
.AllowMultiSelect = True
.InitialFileName = "c:\foldername"
If .Show = True Then
Me![txtPath] = Left$(.SelectedItems(1), InStrRev(.SelectedItems(1),
"\")) & _
Right$(.SelectedItems(1), Len(.SelectedItems(1)) -
InStrRev(.SelectedItems(1), "\"))
End If
end with

This works ok.
Because the foldername in txtPath nearly never changes in my application, I
would like to create a command button which automaticly makes a string with
the name of the most recent file in the txtPath-folder , without opening the
filebox dialog.
Any idea how to start or where I could find it?

Michel
Click to expand...
 
tks John, It works!!, I would never have found it!! - michel

John Nurick said:
Something like this, which works but has not been thoroughly tested:


Public Function LastModifiedFileInFolder(ByVal Folder As String, _
ByVal Wildcard As String)

'Returns the name of the most recently modified file in Folder
'that matches Wildcard. If the timestamps are the same (to the
'nearest second, returns the first file found. If Folder does
'not exist, or no file matches Wildcard, returns an empty
'string.

Dim FileName As String
Dim NewestDate As Date
Dim NewestName As String
Dim FileDate As Date
Const PATH_SEPARATOR = "\"

If Right(Folder, 1) <> PATH_SEPARATOR Then
Folder = Folder & PATH_SEPARATOR
End If
NewestDate = #1/1/1900#
FileName = Dir(Folder & Wildcard)
Do While Len(FileName) > 0
FileDate = CDate(FileDateTime(Folder & FileName))
If FileDate > NewestDate Then
NewestDate = FileDate
NewestName = FileName
End If
FileName = Dir()
Loop
LastModifiedFileInFolder = NewestName
End Function


In Access 2002 I use the following code to open the file dialog box which
allows the user to pick out a folder.

With Application.FileDialog(4)
.AllowMultiSelect = True
.InitialFileName = "c:\foldername"
If .Show = True Then
Me![txtPath] = Left$(.SelectedItems(1), InStrRev(.SelectedItems(1),
"\")) & _
Right$(.SelectedItems(1), Len(.SelectedItems(1)) -
InStrRev(.SelectedItems(1), "\"))
End If
end with

This works ok.
Because the foldername in txtPath nearly never changes in my application,
I
would like to create a command button which automaticly makes a string
with
the name of the most recent file in the txtPath-folder , without opening
the
filebox dialog.
Any idea how to start or where I could find it?

Michel
Click to expand...
Click to expand...
 
You'll want to look at the Microsoft Scripting Runtime library. You'll need
to specifiy the folder of interest. Loop through the files checking the
date. Return the filename with the oldest date. It's pretty straight
forward.

Search around MSDN or Google for "Filesystem object" for examples.
 
Back
Top