How would I write a macro to open the most recent excel or xml file within a
folder? I have file names that start with the same first five letters, but
the end of the file name changes according to the date of the file. On a
daily basis, my macro needs to open the most recent file created in a
specified folder.
--
Thank you, Jodie

Hi Jodie

Give this a try. It will open the most recent Excel file in a
specified folder. Change path to suit.

Take care

Marcus

Option Explicit
Const XLFILE_FOLDER = "C:\" 'Change to suit

Sub RecentXLFile()
Dim fs As Object
Dim objFolder As Object
Dim objFile As Object
Dim myFile As Object
Dim myDate As Date
myDate = DateValue("1/1/1900")

Set fs = CreateObject("Scripting.FileSystemObject")
Set objFolder = fs.GetFolder(XLFILE_FOLDER)

For Each objFile In objFolder.Files
If InStr(objFile.Name, ".xls") 0 Then 'xml can go here too.
If objFile.DateLastModified myDate Then
myDate = objFile.DateCreated
Set myFile = objFile
End If
End If
Next

If Not myFile Is Nothing Then
Workbooks.Open Filename:=myFile
End If

End Sub

This is great!
--
Thank you, Jodie


"marcus" wrote:

Hi Jodie

Give this a try. It will open the most recent Excel file in a
specified folder. Change path to suit.

Take care

Marcus

Option Explicit
Const XLFILE_FOLDER = "C:\" 'Change to suit

Sub RecentXLFile()
Dim fs As Object
Dim objFolder As Object
Dim objFile As Object
Dim myFile As Object
Dim myDate As Date
myDate = DateValue("1/1/1900")

Set fs = CreateObject("Scripting.FileSystemObject")
Set objFolder = fs.GetFolder(XLFILE_FOLDER)

For Each objFile In objFolder.Files
If InStr(objFile.Name, ".xls") 0 Then 'xml can go here too.
If objFile.DateLastModified myDate Then
myDate = objFile.DateCreated
Set myFile = objFile
End If
End If
Next

If Not myFile Is Nothing Then
Workbooks.Open Filename:=myFile
End If

End Sub
.