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 Befo=Workbooks( _
"Workup Template.xls").Sheets(3)
Windows("ForWorkup.xls").Activate
ActiveWorkbook.Close

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

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




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 Befo=Workbooks( _
"Workup Template.xls").Sheets(3)
Windows("ForWorkup.xls").Activate
ActiveWorkbook.Close

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

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




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 Befo=Workbooks( _
"Workup Template.xls").Sheets(3)
Windows("ForWorkup.xls").Activate
ActiveWorkbook.Clos

--
farfu
-----------------------------------------------------------------------
farful's Profile: http://www.excelforum.com/member.php...fo&userid=3617
View this thread: http://www.excelforum.com/showthread.php?threadid=56335

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

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

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




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 Befo=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