ExcelBanter - Finding the last modified directory and file

Hi Shilpa,

You can use the FileSystemObject to do this:

Public Function gsGetLastModFile(rsPath As String, _
rbRecursive As Boolean) As String
Dim fso As Object
Dim fol As Object
Dim fil As Object
Dim folLastMod As Object
Dim filLastMod As Object

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FolderExists(rsPath) Then
If rbRecursive Then
For Each fol In fso.GetFolder(rsPath).SubFolders
If folLastMod Is Nothing Then Set folLastMod = fol
If fol.DateLastModified folLastMod.DateLastModified _
Then Set folLastMod = fol
Next fol
End If

If folLastMod Is Nothing Then Set folLastMod = fso.GetFolder(rsPath)

For Each fil In folLastMod.Files
If filLastMod Is Nothing Then Set filLastMod = fil
If fil.DateLastModified filLastMod.DateLastModified _
Then Set filLastMod = fil
Next fil

Set folLastMod = Nothing
If Not filLastMod Is Nothing Then gsGetLastModFile = filLastMod.Path
Set filLastMod = Nothing
End If
End Function

You may want to add some error handling and do some testing, as I didn't
test this very well.

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

wrote:
Hi,

I am working on a project in which I want to find the latest directory
and the lastest file in that directory.

G:\hweng\ATCA\Compliance\EMC\In house scans\3-1-05\*.xls

Till "In house scans" the directory path is fixed. The 3-1-05
directory name keeps on changing every month. I have no idea as to
how to find the lastest or last modified directory and last modified
excel file.

Is there any function in VBA that will find the latest directory and
file.....

Any help on this will be greatly appreciated.

Regards
Shilpa