Last Workbook in A Folder

[Guest]

Hi,

Which command do I use to locate the last workbook in A Folder
so e.g. My Documents
So Far I have

sub fold()

dim i

i = C:\My Documents\Workbooks.Count
MsgBox C:\My Documents\Workbooks(i)

End Sub

 

[Tom Ogilvy]

If it is in a folder and not open in Excel, then it will not be in the
workbooks collection.

Define what makes a file the last workbook in a folder. Alphabetically, by
the date it was saved, ??

 

[Tim Williams]

What do you mean by "last workbook": based on create date, modified
date, filename.... ?

Tim.

 

[Lonnie M.]

Teresa, this is procedure Dana DeLouis posted that I have modified a
little for my needs. It finds the 'Newest' or last file in a folder.

'########################################################
Public Sub OpenNewestFile(searchDir$)
Dim file20Dir$
'// Modified--Origionally posted by Dana DeLouis
'// The first search is a workaround for an excel bug
'// in order to actually do the search you want
With Application.FileSearch
'// Wake Excel up for msoSortByLastModified to work !
..NewSearch
..LookIn = "C:\"
..FileName = "*.jnk"
..Execute SortBy:=msoSortBySize
End With
With Application.FileSearch
..NewSearch
'searchDir must be passed to the Sub
..LookIn = searchDir
..SearchSubFolders = False
..FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderDescending) > 0 Then
Dim nameArr As Variant
Dim myVar$, myFileName$
myVar = .FoundFiles(1)
nameArr = Evaluate("{""" & Application.Substitute( _
myVar, "\", """,""") & """}")
myFileName = nameArr(UBound(nameArr))

Response = MsgBox("The newest 20 file is " & myFileName & " created " _
& FileDateTime(.FoundFiles(1)) & ";Click OK to open; Click cancel to
select correct 20 file.", vbOKCancel)
If Response <> 2 Then
Workbooks.Open .FoundFiles(1)
Exit Sub
Else
file20Dir = ""
MsgBox "Select the 'Current' download file."
On Error GoTo 0
file20Dir = Application.GetOpenFilename
If file20Dir = "False" Then
MsgBox "You failed to select the 20 file. This will end the process.",
vbOKOnly
Exit Sub
End If
Call fOpen(file20Dir)
End If
Else
MsgBox "There was no 20 file found, select the 'Current' 20 file."
file20Dir = ""
MsgBox "Select the Latest '20 file'."
On Error GoTo 0
file20Dir = Application.GetOpenFilename
If file20Dir = "False" Then
MsgBox "You failed to select the 20 file. This will end the process.",
vbOKOnly
Exit Sub
End If
Call fOpen(file20Dir)
End If

End With
End Sub
'########################################################