finding a file name

[Mark Scholes]

Hi,
I want to open a directory and open a spread sheet in it
based on the newest Modified date. The spread sheet can
have any name but it must be the newest one.

Thanks MarkS

 

[Patrick Molloy]

In the IDE, under the tools menu, set a reference to
Microsoft Scripting Runtime.
We'll use the FileSystemObject and File object from this
library to get the fiels and test the last modified date.

Once you've set the reference, add the following code to
a standard code module


Sub GetFiles()

Dim wb As Workbook
Dim fso As Scripting.FileSystemObject
Dim ThisFile As Scripting.File
Dim Path As String
Dim LatestFile As Scripting.File
Path = "C:\Temp\"

Set fso = New Scripting.FileSystemObject

For Each ThisFile In fso.GetFolder(Path).Files
If ThisFile.Name Like "*.xls" Then
If LatestFile Is Nothing Then
Set LatestFile = ThisFile
ElseIf ThisFile.DateLastModified > _
LatestFile.DateLastModified Then
Set LatestFile = ThisFile
End If
End If

Next

If LatestFile Is Nothing Then
MsgBox "No Workbooks Found"
Else
Workbooks.Open LatestFile
End If


End Sub

FYI. Excel Files are genrally called Excel Workbooks.
Workbooks generally contain collections of worksheets
among other things. Its a great idea to read up on the
Excel Object Model...for anyone looking to get started in
VBA programming.


Patrick Molloy
Microsoft Excel MVP