Using VBA To Search For The Latest Version Of A Text File

If you're using VBA to import data from a text file, you might need to make sure you're using the latest version of the file. This article will show you how to use VBA to search a folder and identify the correct document based on the date and time the file was created.
One example could be that you receive daily files which are the basis for a regular report you compile in Excel.

18-3-2012Report.csv

19-3-2012Report.csv

20-3-2012Report.csv

Listing Files With The File System Object
The Microsoft Scripting Runtime library must be selected under references in the tools tab before any code you write can access the file system.
Our short piece of VBA code will select all the files in the appropriate folder and find the file with the most recent file creation date.
First, we'll create the file system object and select the folder, which in this example is called "files" and is under the folder of the active workbook.

Dim fso As Scripting.FileSystemObject

Dim SourceFolder As Scripting.FolderSet fso = New Scripting.FileSystemObject

Set SourceFolder = fso.GetFolder(ActiveWorkbook.path & "\files")

Next, we can loop through the folder and determine the most recent file. We do this by creating a date object, which by default defines the year as 1899 - depending on the version of Excel you have.
To begin, we set the newest date as "30/12/1899" so every file will have been created after that date. As we loop through the code we redefine the newest date and file depending on the file creation date.

Dim d As Date

newestFile = ""

newestDate = d

End If

Next

Debug.Print newestFile; ": " & newestDate

At the end of the loop we print the file name and the date created which gives an output like this:

20-3-2012Report.csv: 10/11/2012 8:25:41 p.m.

If your files are named with the dates as part of a naming structure, it might be tempting to parse out the date from the file name. While this would probably work, it means you don't really have any control. For example, if the file's creator decides to use a different naming structure you would end up with a bug which might take valuable time to identify.