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

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
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
'-------------------------------



<scott.k@ems-t.com>
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

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.

Originally Posted by 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
'-------------------------------



<scott.k@ems-t.com>
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

--
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"
<farful.2b960t_1153413615.1968@excelforum-nospam.com>
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.

Jim Cone Wrote:
> 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
> '-------------------------------
>
>
>
> <scott.k@ems-t.com>
> 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
------------------------------------------------------------------------
farful's Profile: http://www.excelforum.com/member.php...o&userid=36172
View this thread: http://www.excelforum.com/showthread...hreadid=563358