find newest version of file , copy and paste into new folder

[muss1210]

Hello forum

I need some help where i have to copy and paste the newest file starting SBAL from one folder "X:\EMEA\Logistics " to another folder "X:\EMEA\Stats "
The file name is in this format SBAL20190605080325 - YYYYMMDD , i presume the last 6 digits are seconds so the file name always changes


Thanks in advance

 

[Yongle]

Try this to see if it moves the correct file
- the message box tells you what was moved

Check that folder names are correct
- I have ignored trailing spaces included in post#1
- end paths with separator \

Sub LoopThroughFiles()

    Const wildCard = "SBAL*"
    Const fromPath = "[COLOR=#b22222]X:\EMEA\Logistics\[/COLOR]"
    Const toPath = "[COLOR=#b22222]X:\EMEA\Stats\[/COLOR]"
    Const T = vbCr & vbTab
    Const L = vbCr
    
    Dim file As String, latest As String, latestDate As Date, latestDate_temp As Date

[I][COLOR=#006400]'loop files in folder[/COLOR][/I]    
    file = Dir(fromPath & wildCard)
    Do While Len(file) > 0
        latestDate_temp = FileDateTime(fromPath & file)
        If latestDate_temp > latestDate Then
            latestDate = latestDate_temp
            latest = file
        End If
        file = Dir
    Loop
    
[I][COLOR=#006400]'move the file[/COLOR][/I]
    Name fromPath & latest As toPath & latest
    MsgBox "MOVE:" & T & fromPath & latest & L & L & "TO:" & T & toPath & latest
End Sub

 

[muss1210]

Thanks Yongle that works great but it cuts and pastes the file rather than copy and paste, the folder i am copying from is from our WMS system so the files cant be modified or cut from the folder due to access rights.

Thanks

 

[Yongle]

try this ...

Sub LoopThroughFiles()
    Const wildCard = "SBAL*"
    Const fromPath = "X:\EMEA\Logistics\"
    Const toPath = "X:\EMEA\Stats\"
    Const L = vbCr & vbCr
    Dim file As String, latest As String, msg As String, latestDate As Date, latestDate_temp As Date

[COLOR=#006400][I]'loop files in folder[/I][/COLOR]
    file = Dir(fromPath & wildCard)
    Do While Len(file) > 0
        latestDate_temp = FileDateTime(fromPath & file)
        If latestDate_temp > latestDate Then
            latestDate = latestDate_temp
            latest = file
        End If
        file = Dir
    Loop
[I][COLOR=#006400]'copy the file[/COLOR][/I]
    With CreateObject("Scripting.FileSystemObject")
        Select Case False
            Case .FileExists(toPath & latest)
                .CopyFile (fromPath & latest), toPath, True
                msg = latest & L & "copied to" & L & toPath
            Case Else
                msg = "OOPS! " & L & latest & L & "ALREADY EXISTS in " & L & toPath
        End Select
    End With
    MsgBox msg, , ""
End Sub

Q What should happen if file already exists in destination folder? Should user be given option to overwrite ?

 

[muss1210]

happy days

That works like a dream

To answer your question, there wont be another file, i have another macro that will rename the file once moved and the old file gets deleted before the new file is renamed.