How to identify last file created in a directory ???? 3

[dschomburg]

I seem to be missing the entire help library for FileSystemObect. And to get my tech support to load it will be next to impossible.

What I'm trying to do is look in a directory such as "C:\thisdirectory" and identify the most recently created file. (and then import it as a table).

The files all start with the same file name prefix but then use a time/date stamp appended afterwards. So I guess the file attributes is my only way to identify the proper file to import. But I'm really sure how to do this.

Any help would be greatly appreciated.
Thanks,
Dave

 

[EBGreen]

If the name has a date/time stamp in it, then you could use the file name or the file properties whichever you'd rather do. To use the file properties, then use a bubble sort (or whatever sorting algorithm you prefer) to sort on each file's DateLastModified property. If I get a chance, I will code up an example later. As for getting the docs installed on your machine, you could download them yourself, or access a good substitute for them online at

http://www.devguru.com.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[dschomburg]

Spooooon!

Thank you very much for your reply. I've been playing around with some code but I just can't seem to come up with a way to say "MAX(DateCreated)" kind of thing.

If that makes any sense.

If you do have time to come up with an example that would be fantastic!

Thanks again,
Dave

 

[CajunCenturion]

Are you trying to use the FSO object, just don't know how to use the FSO? Or do you want to do this without the FSO?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[EBGreen]

An excellent question Cajun. Dave, how are you currently attempting to do this?

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[dschomburg]

I was attempting to use fso but that's where I'm getting stuck. How would I properly use DateCreated to identify the correct file. I.E. the latest file created would be the one to import.

 

[CajunCenturion]

Given that you've only found 4 post helpful in the 100 questions that you've asked, I hesitate to spend much time on it, but since I already have working code I'll go ahead.

The following code will return the last file created in the specified directory.

Private Sub cmdFSO_Click()

   Dim lObj_FSO         As Scripting.FileSystemObject
   Dim lObj_Folder      As Scripting.Folder
   Dim lObj_File        As Scripting.File
   Dim lStr_NewestFile  As String
   Dim lStr_NewestDate  As String
   
   lStr_NewestFile = vbNullString
   lStr_NewestDate = Format("01/01/01", "yyyymmdd")
   
   Set lObj_FSO = New Scripting.FileSystemObject
   Set lObj_Folder = lObj_FSO.GetFolder("C:\SomeDir\SomeSubDir")
   For Each lObj_File In lObj_Folder.Files
      If (Format(lObj_File.DateCreated, "yyyymmdd") > lStr_NewestDate) Then
         lStr_NewestDate = Format(lObj_File.DateCreated, "yyyymmdd")
         lStr_NewestFile = lObj_File.Name
      End If
   Next lObj_File
   
   If (Len(lStr_NewestFile) > 0) Then
      MsgBox "Newest File = " & lStr_NewestFile & vbCrLf & _
             "Created on = " & Mid(lStr_NewestDate, 5, 2) & "\" & _
                               Right(lStr_NewestDate, 2) & "\" & _
                               Left(lStr_NewestDate, 4)
   Else
      MsgBox "No files in Directory"
   End If
   
   Set lObj_File = Nothing
   Set lObj_Folder = Nothing
   Set lObj_FSO = Nothing
   
End Sub

I hope that in the future dschomburg you do mark those posts you find helpful not only to show some gratitude to those volunteered their time to help you, but primarily to make it easier on your fellow IT professionals to find helpful answers they seek especially when searching the thread database. It's more in keeping with the Give and Take spirit of the site.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[EBGreen]

Here is my solution, but Cajun usually has better ones:

Private Sub Command13_Click()
    Dim oFSO As FileSystemObject
    Dim oFolder As Folder
    Dim LastFile As File
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("C:\Temp")
    Set LastFile = GetLastFile(oFolder)
    MsgBox LastFile.Name
    
    
End Sub

Private Function GetLastFile(oFolder As Folder)
    Dim oDict As Scripting.Dictionary
    Dim colFiles As Files
    Dim oFile As File
    Dim dtDateLastMod As Date
    
    Set colFiles = oFolder.Files
    dtDateLastMod = CDate("1/1/1950")
    For Each oFile In colFiles
        If oFile.DateLastModified > dtDateLastMod Then
            dtDateLastMod = oFile.DateLastModified
            Set GetLastFile = oFile
        End If
    Next
    
End Function

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[dschomburg]

Thanks Tom! Your solution works like a charm!
Your time and your knowledge is greatly appreciated.

I'll need to learn more about scripting.dictionary