Identify and Copy latest files in directory

Question

Everyday around 7 AM there are 3 csv exports extracted into a specific folder and the file names are exactly the same each day except everyday the prefix of the file name is amended to the current date.

Example:

16-02-2018_Test1 will change to 17-02-2018_Test1

16-02-2018_Test2 will change to 17-02-2018_Test2

16-02-2018_Test3 will change to 17-02-2018_Test3

The file itself is not replaced, the new file with the current date is instead added to this folder.

What I need to do is identify the 3 extracts each day and copy them to a sub-folder. The best way I thought of doing this is by identifying the date at which the file was last modified.

I have the below VBS code I found and helps identifies the latest file in a directory and I added a line that will copy that file to a new directory.

The issue however, is that the code only identifies 1 file instead of 3 and I can only copy 1 file instead of 3. If anyone has better code to help me achieve the desired result or alternatively can help modify the existing code to achieve the desired result.

sPath = "C:\Users\Desktop\Test\"

Const sToDir = "C:\Users\Desktop\Test\NewFolder\"

Set oFSO = CreateObject("Scripting.FileSystemObject")

sNewestFile = GetNewestFile(sPath)

If sNewestFile <> "" Then

   WScript.Echo "Newest file is " & sNewestFile

   dFileModDate = oFSO.GetFile(sNewestFile).DateLastModified
   If DateDiff("n", dFileModDate, Now) > 60 Then
     oFSO.CopyFile sNewestFile, sToDir
   End If

Else
   WScript.Echo "Directory is empty"
End If


Function GetNewestFile(ByVal sPath)

   sNewestFile = Null   ' init value

   Set oFSO = CreateObject("Scripting.FileSystemObject")
   Set oFolder = oFSO.GetFolder(sPath)
   Set oFiles = oFolder.Files

   ' enumerate the files in the folder, finding the newest file
   For Each oFile In oFiles
     On Error Resume Next
     If IsNull(sNewestFile) Then
       sNewestFile = oFile.Path
       dPrevDate = oFile.DateLastModified
     Elseif dPrevDate < oFile.DateLastModified Then
       sNewestFile = oFile.Path
       dPrevDate = oFile.DateLastModified
     End If
     On Error Goto 0
   Next

   If IsNull(sNewestFile) Then sNewestFile = ""

   GetNewestFile = sNewestFile

End Function

Have you tried parsing names of the files to extract the date? — hrust, Feb 19 '18 at 12:23
Hi @hrust No I have not. How would having the names help identify the most recent files? — Eitel Dagnin, Feb 19 '18 at 13:56
Isn't this `16-02-2018_Test1.csv` an example of the filename you're processing? If you have files with the date in their names then it's very simple to locate required ones. — hrust, Feb 19 '18 at 14:11

score 1 · Answer 1 · answered Feb 19 '18 at 20:42

Invest some work in a useful format class and

just look for the 3 files of the day (FileExists)
if they are not there, look for the previous day's files

or

search for the newest file and build all three file names from the prefix

In code:

Option Explicit

' stolen from https://stackoverflow.com/a/21643663/603855
' added formatTwo; left as exercise: formatThree
Class cFormat
  Private m_oSB
  Private Sub Class_Initialize()
    Set m_oSB = CreateObject("System.Text.StringBuilder")
  End Sub ' Class_Initialize
  Public Function formatOne(sFmt, vElm)
    m_oSB.AppendFormat sFmt, vElm
    formatOne = m_oSB.ToString()
    m_oSB.Length = 0
  End Function ' formatOne
  Public Function formatTwo(sFmt, vElm1, vElm2)
    m_oSB.AppendFormat_2 sFmt, vElm1, vElm2
    formatTwo = m_oSB.ToString()
    m_oSB.Length = 0
  End Function ' formatOne
  Public Function formatArray(sFmt, aElms)
    m_oSB.AppendFormat_4 sFmt, (aElms)
    formatArray = m_oSB.ToString()
    m_oSB.Length = 0
  End Function ' formatArray
End Class ' cFormat

Dim oFmt : Set oFmt = New cFormat
Dim sFmt : sFmt = "{0:dd-MM-yyyy}_Test{1}"
Dim dToday : dToday = Date()
Dim i 
WScript.Echo "file names expected today " & oFmt.formatOne("({0:yyyy-MMM-d}).", dToday) 
For i = 1 To 3
    WScript.Echo oFmt.FormatTwo(sFmt, dToday, i)
Next
WScript.Echo oFmt.formatArray("look for {0} if {1} is missing on the {2:dd}th after 7 AM" _ 
   , Array(oFmt.FormatTwo(sFmt, DateAdd("d", -1, dToday), 1), oFmt.FormatTwo(sFmt, dToday, 1), dToday))

Dim sFnd : sFnd = oFmt.FormatTwo(sFmt, dToday, 2) 
WScript.Echo "if your GetNewestFile() finds " & sFnd & ", copy:"
For i = 1 To 3
    WScript.Echo Left(sFnd, Len(sFnd) - 1) & i 
Next

output:

cscript 48866113.vbs
file names expected today (2018-Feb-19).
19-02-2018_Test1
19-02-2018_Test2
19-02-2018_Test3
look for 18-02-2018_Test1 if 19-02-2018_Test1 is missing on the 19th after 7 AM
if your GetNewestFile() finds 19-02-2018_Test4, copy:
19-02-2018_Test1
19-02-2018_Test2
19-02-2018_Test3

score 1 · Accepted Answer · answered Feb 20 '18 at 06:19

Thanks to everyone for the help, I found the answer on a different thread. Here is the link: Copy 2 latest text file from a source folder to destination folder

Below is the code:

Option Explicit

Dim FolderToCheck, FolderDestination, FileExt, mostRecent, noFiles, fso, fileList, file, filecounter, oShell, strHomeFolder

' Enumerate current user's home path - we will use that by default later if nothing specified in commandline
Set oShell = CreateObject("WScript.Shell")
strHomeFolder = oShell.ExpandEnvironmentStrings("%USERPROFILE%")

'Variables -----
folderToCheck = strHomeFolder & "\Desktop\MY\MMS"           ' Folder Source to check for recent files to copy FROM
folderDestination = strHomeFolder & "\Desktop\New"          ' Destination Folder where to copy files TO

fileExt = "txt"     ' Extension we are searching for
mostRecent = 2      ' Most Recent number of files to copy
' --------------


PreProcessing()     ' Retrieve Command Line Parameters

' Display what we are intending on doing
wscript.echo "Checking Source: " & FolderToCheck 
wscript.echo "For Files of type: " & FileExt
wscript.echo "Copying most recent "& mostRecent &" file(s) to: " & FolderDestination & "."
wscript.echo 

noFiles = TRUE

Set fso = CreateObject("Scripting.FileSystemObject")

Set fileList = CreateObject("ADOR.Recordset")
fileList.Fields.append "name", 200, 255
fileList.Fields.Append "date", 7
fileList.Open

If fso.FolderExists(FolderToCheck) Then 
    For Each file In fso.GetFolder(FolderToCheck).files
     If LCase(fso.GetExtensionName(file)) = LCase(FileExt) then
       fileList.AddNew
       fileList("name").Value = File.Path
       fileList("date").Value = File.DateLastModified
       fileList.Update
       If noFiles Then noFiles = FALSE
     End If
    Next
    If Not(noFiles) Then 
        wscript.echo fileList.recordCount & " File(s) found. Sorting and copying last " & mostRecent &"..."
        fileList.Sort = "date DESC"
        If Not(fileList.EOF) Then 
            fileList.MoveFirst
            If fileList.recordCount < mostRecent Then 
                wscript.echo "WARNING: " & mostRecent &" file(s) specified but only " & fileList.recordcount & " file(s) match criteria. Adjusted to " & fileList.RecordCount & "."
                mostRecent = fileList.recordcount
            End If

            fileCounter = 0
            Do Until fileList.EOF Or fileCounter => mostRecent
                If Not(fso.FolderExists(folderDestination)) Then 
                    wscript.echo "Destination Folder did not exist. Creating..."
                    fso.createFolder folderDestination
                End If
                fso.copyfile fileList("name"), folderDestination & "\", True
                wscript.echo  fileList("date").value & vbTab & fileList("name")
                fileList.moveNext
                fileCounter = fileCounter + 1
            Loop
        Else
            wscript.echo "An unexpected error has occured."
        End If
    Else
        wscript.echo "No matching """ & FileExt &""" files were found in """ & foldertocheck & """ to copy."
    End If
Else
    wscript.echo "Error: Source folder does not exist """ & foldertocheck & """."
End If

fileList.Close

Function PreProcessing
    Dim source, destination, ext, recent

    ' Initialize some variables
    Set source = Nothing
    Set destination = Nothing
    Set ext = Nothing
    Set recent = Nothing

    'Get Command Line arguments
    ' <scriptname>.vbs /Source:"C:\somepath\somefolder" /Destination:"C:\someotherpath\somefolder" /ext:txt /recent:2

    source = wscript.arguments.Named.Item("source")
    destination = wscript.arguments.Named.Item("destination")
    ext = wscript.arguments.Named.Item("ext")
    recent = wscript.arguments.Named.Item("recent")

    If source <> "" Then FolderToCheck = source
    If destination <> "" Then FolderDestination = destination
    If ext <> "" Then FileExt = ext
    If recent <> "" Then mostRecent = int(recent)

End Function

Identify and Copy latest files in directory

2 Answers2

Linked