VBA determine most recent file in a folder get its name move to backup folder

Thrawn3000 used Ask the Experts™
Hi Experts
I need some VBA code to allow me to determine the most recent file in a folder get its name and move it to an archive folder.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I wanted to do the same thing but wrote a simple vbscript instead. Below is the code. Copy it into notepad. Change the strloc and strdest values to the appropriate directories. Save it with a .vbs extension and double click it to run.
Set objFSO = CreateObject("Scripting.FileSystemObject")
strLoc = "\Documents\"
strdest = "\Backups\"

Set f = objfso.getfolder(strLoc)
set fc = f.files
d=datevalue("July 11, 2000")'dummy initial date

for each f1 in fc
	if datevalue(f1.DateCreated)>d then
		d = datevalue(f1.DateCreated)
                file = f1.name
	end if

If d = datevalue("July 11, 2000") then
    msgbox "No files"
end if

objFSO.CopyFile strloc & file, strdest	
MsgBox "File Saved"
end if

Open in new window


That works great, but I rally need it in vba tloth01 any ideas on how to convert this ?
Top Expert 2006
how about the code from here? http://www.mrexcel.com/forum/showthread.php?t=223730

attached code is it tweaked

Note myDir which specifies c:\temp as the folder to look in

then look for comments  'HERE IS YOUR FILENAME

and make a note of FileCopy, I am copying that file to c:\backup\
Sub GetMostRecentFile()
    Dim FileSys As FileSystemObject
    Dim objFile As file
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
    'set path for files - change for your folder
    Const myDir As String = "c:\temp\"
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.files
        If objFile.DateLastModified > dteFile Then
            dteFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    Next objFile
    MsgBox "Last Filename is " & strFilename
    FileCopy myDir & strFilename, "c:\backup\" & strFilename
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

Open in new window

Top Expert 2006

I am looking at the code posted by tloth01 and see a lot of similarities. As I have given the source of my code (mrexcel), I hope tloth01 does not think I have copied his/her code.

In addition, Ive put in the line to copy the file to another folder


Thanks guys, it does what I neeed it to do split the points between you


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial