VBA: Open Most Current File In Specified Folder

  • Thread starter Thread starter whicks
  • Start date Start date
W

whicks

I'm setting up a module that will grab a specific .xls and organize it
for production as well as load the information into Crystal for
archiving. In the past, I have always directed the user to save
the .xls as "AnyFileName" in a specific folder and the module would
pick it up at a scheduled time. This has me relying on the user to
not make any mistakes in naming the file.

Is there a produre in VBA that will open the most current file in a
specified folder?
 
The macro below should work OK.

HTH,
Bernie
MS Excel MVP


Sub FindNewestFile()
With Application.FileSearch
'// The following is a workaround that has worked for Dana DeLouis
'// Wake Excel up for msoSortByLastModified to work !
.NewSearch
.LookIn = "C:\"
.FileName = "*.xls"
.Execute SortBy:=msoSortBySize
End With
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Documents and Settings\UserName\My Documents\Excel"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then
MsgBox "The newest file is " & .FoundFiles(1) & " created " & FileDateTime(.FoundFiles(1))
Workbooks.Open .FoundFiles(1)
Else
MsgBox "There were no files found."
End If
End With
End Sub
 
The macro below should work OK.

HTH,
Bernie
MS Excel MVP

Sub FindNewestFile()
With Application.FileSearch
   '// The following is a workaround that has worked for Dana DeLouis
   '// Wake Excel up for msoSortByLastModified to work !
   .NewSearch
   .LookIn = "C:\"
   .FileName = "*.xls"
   .Execute SortBy:=msoSortBySize
End With
With Application.FileSearch
   .NewSearch
   'Change this to your directory
   .LookIn = "C:\Documents and Settings\UserName\My Documents\Excel"
   .SearchSubFolders = False
   .FileType = msoFileTypeExcelWorkbooks
   If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then
      MsgBox "The newest file is " & .FoundFiles(1) & " created " &FileDateTime(.FoundFiles(1))
       Workbooks.Open .FoundFiles(1)
   Else
      MsgBox "There were no files found."
   End If
End With
End Sub







- Show quoted text -
Click to expand...

Thanks! I didn't know where to start and this looks solid.
 
Back
Top