Selecting the most recent file in a folder by Macro/VBA script

[scott.k]

My spreadsheet has a macro that currently auto opens a file with a
specified name from a specified location then takes a series of
actions. I'd like to have a script that will go to the folder and open
the most recent file, regardless of what it is named. Any advice is
appreciated!

Here is the part of the macro that currently opens the file and brings
over info to the active workbook:

Workbooks.Open Filename:= _
"H:\DOWNLOAD\CURRENT\Download for Workup\ForWorkup.xls"
Sheets("DOWNLOAD CURRENT EMPLOYEES 3").Copy Before:=Workbooks( _
"Workup Template.xls").Sheets(3)
Windows("ForWorkup.xls").Activate
ActiveWorkbook.Close

 

[Jim Cone]

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub LatestFile()
'Jim Cone - San Francisco, USA - June 02, 2005
'Displays the latest file name in the strPath folder.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim varDate As Variant

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office\Library"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified > varDate Then
varDate = objFile.DateLastModified
strName = objFile.Name
End If
Next 'objFile

' Display file name in message box.
MsgBox strName & " - is latest file - " & varDate

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
'-------------------------------



<[email protected]>
wrote in message
My spreadsheet has a macro that currently auto opens a file with a
specified name from a specified location then takes a series of
actions. I'd like to have a script that will go to the folder and open
the most recent file, regardless of what it is named. Any advice is
appreciated!

Here is the part of the macro that currently opens the file and brings
over info to the active workbook:

Workbooks.Open Filename:= _
"H:\DOWNLOAD\CURRENT\Download for Workup\ForWorkup.xls"
Sheets("DOWNLOAD CURRENT EMPLOYEES 3").Copy Before:=Workbooks( _
"Workup Template.xls").Sheets(3)
Windows("ForWorkup.xls").Activate
ActiveWorkbook.Close

 

[farful]

Hi Jim,
What would I need to add if I wanted to use this macro, but only seac
files beginning with a particular string? That is, find the las
modified file given that a string must be in the beginning of a fil
name.

 

[Jim Cone]

--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html

Sub LatestFileWithName()
'Jim Cone - San Francisco, USA - July, 2006
'Displays the latest file name in the strPath folder,
' if the file name contains a specified word.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim varDate As Variant
Dim strFind As String

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office\Library"
' Specify the word in the file name...
strFind = "Mush"

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If InStr(1, objFile.Name, strFind, vbTextCompare) Then
If objFile.DateLastModified > varDate Then
strName = objFile.Name
varDate = objFile.DateLastModified
End If
End If
Next 'objFile

' Display file name in message box.
If Len(strName) = 0 Then
strName = "None found"
Else
strName = strName & " - is latest file - " & varDate
End If
MsgBox strName, , " Latest File"

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
'-------------


"farful"
<[email protected]>
wrote in message

Hi Jim,
What would I need to add if I wanted to use this macro, but only seach
files beginning with a particular string? That is, find the last
modified file given that a string must be in the beginning of a file
name.