I need to look in a specific folder and find the last file saved and move(or copy) to an other folder using VBA.

by finding the file i'm using:

Private Function fFindLastFile()

'Call GetFolder

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim myFolder As Object
Set myFolder = fso.GetFolder("D:\SF\C0T460A220000042\")
'Set myFolder = fso.GetFolder(GetFolder)
Dim myFile As Object
Set myFile = myFolder.Files.Item(myFolder.Files.Count)      '<----- this is where i get a debug nr 5, unknown procedure or argument
MsgBox myFile.Name & " was last modified on " & myFile.DateLastModified

End Function

I don not have the name or type of the file that i'm looking for, but i just downloaded it from a known URL.
do you have any ideas what i'm doing wrong?

CodePudding user response：

Last Modified File (FileSystemObject)

Option Explicit

Private Sub CopyLastFile()

    Const sFolderPath As String = "D:\SF\C0T460A220000042\"
    Const dFolderPath As String = "C:\Test\" ' adjust!

    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    Dim fsoFolder As Object: Set fsoFolder = fso.GetFolder(sFolderPath)

    Dim fsoFile As Object, fName As String, fDate As Date
    
    For Each fsoFile In fsoFolder.Files
        If fsoFile.DateLastModified > fDate Then
            fName = fsoFile.Name
            fDate = fsoFile.DateLastModified
        End If
    Next fsoFile

    If Len(fName) > 0 Then
        fso.CopyFile sFolderPath & fName, dFolderPath, True
        'fso.MoveFile sFolderPath & fName, dFolderPath, True
        MsgBox "File Name: " & fName & vbLf & "Last modified: " & fDate, _
            vbInformation, "Last Modified File"
    Else
        MsgBox "No file found.", vbExclamation, "Last Modified File"
    End If

End Sub