VBA Dynamic variable

[Ananthak275]

Trying to create a script that will open a directory, select the file and assign the workbook name and worksheet name to variables. The issue is the file in a unique format, below is a sample of the list of files. How do I select the most recent one (the bolded one)?

newfile_2021-03-11
newfile_2021-02-11
newfile_2021-04-11
newfile_2021-05-11

Currently my code is :

Sub OpenWorkbooksAndChangeNames
Workbooks.open("\\datafiles\newfile*xl.xlsx" <- as you can see im trying to figure out how to open the newest file

Dim wb As Workbook
Dim ws As Worksheet

Set wb = DatraFile_Workbook
Set ws = DataFile_Sheet1 <- this is the second part of my code. Assigning the opened newfile_2021-05-11 to a variable (wb) along with the first sheet name (ws)

 

[Marc L]

​

Use the VBA function Dir - a VBA help must read ! - to list each file, extract the date of each file name in order to compare and to keep the last one​

but as a trick often the last one listed under this function is the more recent according to your static format 'newfile_YYYY-MM-DD' …​