Excel VBA: How to open the most recent excel file

Question

I download files on a specified path, but every time, in order to open the most recent file I have to rename the recent file to a specified name that I have set in the macro.

As long as the path of the file doesn't change, I want the macro to open the most recent file. For example, given two files my_file_fb and my_file_fb(1), I want to open the most recently downloaded file.

Well, you can use [FileDateTime()](https://www.techonthenet.com/excel/formulas/filedatetime.php) to get the date of a file's creation, and [Dir](https://stackoverflow.com/a/10382861/6609896) to loop through files with wild card. Then just compare dates to find the most recent. — Greedo, Nov 11 '17 at 18:02
Well what have you tried so far? What code are you using to compare the dates returned, or to open the files? SO is not really here as a code writing service and so it's best to show what you've done so far. Try writing something using those links I suggested and explain where precisely you have got stuck. — Greedo, Nov 11 '17 at 18:18
I am really new to VBA, so the only thing I know is how to open a specific file from a specific destination. But yeah, I will go through the links and try to work something out. — Kaleembukhari, Nov 11 '17 at 18:21

score 1 · Answer 1 · answered Nov 11 '17 at 18:53

As per my comments, this function should do the trick:

Public Function MostRecentFile(ByVal searchDirectory As String, ByVal wildCard As String) As String
    '''Returns the most recent file in searchDirectory, which matches wildCard criteria
    Dim strFile As String                        'holds the name of the file we're currently looking at
    Dim mostRecent As Date                       'holds the date of creation for the most recent file
    Dim currDate As Date                         'date of creation for the current file
    Dim mostRecentPath As String                 'path of file with most recent date

    strFile = Dir(searchDirectory & wildCard)    'look for file in directory which matches wildcard
    Do While Len(strFile) > 0                    'loop until Dir returns empty quotes (no files)
        currDate = FileDateTime(searchDirectory & strFile)
        If currDate > mostRecent Then            'check whether current file is more recent than previous files
            mostRecent = currDate                'if so, update most recent date and file
            mostRecentPath = searchDirectory & strFile
        End If
        strFile = Dir                            'move to next file in directory
    Loop

    If mostRecent = 0 Then                       'check whether any files were returned
        MostRecentFile = "No files match '" & searchDirectory & wildCard & "'"
    Else
        MostRecentFile = mostRecentPath
    End If
End Function

It takes input strings searchDirectory and wildCard, the first specifies which folder to look in, the second specifies the type of file to search for.

e.g.

MostRecentFile("C:/Users/[USERNAME]/Downloads/", "*.xls")

Returns the path to the most recent file from the ".xlsm",".xls",".xlsx" (excel files) in your downloads folder as a String

I've added code comments so hopefully you can learn what each step is doing

Thanks alot. I will give it a try. By the way, I tried using the Dir function. The code runs without an error, however, it doesn't return any file name in the msg box. Here is the syntax. excel_file = Dir("Macintosh HD:Users:kaleembukhari:Documents", MacID("Sting.xls:")) — Kaleembukhari, Nov 12 '17 at 08:03
@Kaleembukhari Ahh, this approach will not work for macs as `DIR` does not work with wildcards on a mac. You will have to search the site for mac answers. [This workbook](https://www.rondebruin.nl/mac/mac013.htm) might help. But you must always use the [excel-vba-mac](https://stackoverflow.com/questions/tagged/excel-vba-mac) tag (or equivalent) in your questions, as this is crucial information in many answers — Greedo, Nov 12 '17 at 10:07

Excel VBA: How to open the most recent excel file

1 Answers1