Opening latest files

Mark77 · Oct 15, 2006

I use the following code to open the latest file. What I am trying to do is get this code to find the latest 7 files instead of just one. Can this code be amended? I have tried to include a loop but without sucess!

Any help would be greatly appreciated.

Dim I As Integer
Dim X As Date
Dim LastCreated As String
With Application.FileSearch
.NewSearch
.LookIn = "M:\Ltsb_MI\LTSB\Activity\Weekly"
.Filename = "*.xls"
.Execute
For I = 1 To .FoundFiles.Count

If FileDateTime(.FoundFiles(I)) > X Then
X = FileDateTime(.FoundFiles(I))
LastCreated = .FoundFiles(I)
End If
Next I
Workbooks.Open LastCreated
End With

Legacy 98055 · Oct 15, 2006

Example sub demonstrates returning a sorted list of file path's. They are sorted by DateLastModified.

Opening latest files 238007.zip

<table width="100%" border="1" bgcolor="White" style="filter

rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> Sub Example()
 Dim MyFiles() As String, x As Integer

 MyFiles = GetFilesByDateLastModified("M:\Ltsb_MI\LTSB\Activity\Weekly")

 For x = 0 To UBound(MyFiles)
 Debug.Print MyFiles(x)
 Next

 End Sub

 'contains references to mscorlib and Microsoft Scripting Runtime
 Function GetFilesByDateLastModified(DirectoryPath As String) As String()
 Dim al As New mscorlib.ArrayList, fso As New Scripting.FileSystemObject, f As Scripting.File
 Dim x As Long, TempString() As String

 For Each f In fso.GetFolder(DirectoryPath).Files
 al.Add f.DateLastModified & "|" & f.Path
 Next

 al.Sort
 al.Reverse
 ReDim TempString(0 To al.Count - 1)
 For x = 0 To al.Count - 1
 TempString(x) = Split(al(x), "|")(1)
 Next

 GetFilesByDateLastModified = TempString
 End Function

</td></tr></table><button onclick='document.all("10152006112133218").value=document.all("10152006112133218").value.replace(/ \s\s/g,"");document.all("10152006112133218").value=document.all("10152006112133218").value.replace(/ /g,"");window.clipboardData.setData("Text",document.all("10152006112133218").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="10152006112133218" wrap="virtual">
Sub Example()
Dim MyFiles() As String, x As Integer

MyFiles = GetFilesByDateLastModified("M:\Ltsb_MI\LTSB\Activity\Weekly")

For x = 0 To UBound(MyFiles)
Debug.Print MyFiles(x)
Next

End Sub

'contains references to mscorlib and Microsoft Scripting Runtime
Function GetFilesByDateLastModified(DirectoryPath As String) As String()
Dim al As New mscorlib.ArrayList, fso As New Scripting.FileSystemObject, f As Scripting.File
Dim x As Long, TempString() As String

For Each f In fso.GetFolder(DirectoryPath).Files
al.Add f.DateLastModified & "|" & f.Path
Next

al.Sort
al.Reverse
ReDim TempString(0 To al.Count - 1)
For x = 0 To al.Count - 1
TempString(x) = Split(al(x), "|")(1)
Next

GetFilesByDateLastModified = TempString
End Function
</textarea>

Opening latest files 238007.zip

Mark77 · Oct 15, 2006

Thank you for your reply. I have copied and come across an error in the mscorlib.arraylist.

I have checked the references to ensure that it is enabled and cannot find it, I am probably being very stupid but can you help here?

Thanks once again

Legacy 98055 · Oct 15, 2006

No. It may not be your fault. I am assuming that you hve the net framework installed. Version 1.1 or higher. Did you download the example and check to see if it compiled?

Mark77 · Oct 15, 2006

I downloaded the file and I can see it in the VB window. I cannot see net framework, if I am not mistaken this can be downloaded from Microsoft the only trouble is that our pcs are more locked down these days than ever.

Is there another way I could tackle this?

Legacy 98055 · Oct 15, 2006

Ok. This is faster anyway. Returns an array of file paths from a given folder, sorted in descending order by the last modified date...

Opening latest files 1142342.zip

<table width="100%" border="1" bgcolor="White" style="filter

rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> Option Explicit

 Sub Example()
 Dim MyFiles() As String, x As Integer

 MyFiles = GetFilesSortedByDateLastModified("C:\")

 For x = LBound(MyFiles) To UBound(MyFiles)
 Debug.Print MyFiles(x)
 Next

 End Sub

 Function GetFilesSortedByDateLastModified(DirectoryPath As String) As String()
 Dim fso As Object, f As Object, ff As Object
 Dim x As Long, Dates() As Date, Paths() As String

 Set fso = CreateObject("Scripting.FileSystemObject")
 Set ff = fso.GetFolder(DirectoryPath).Files
 ReDim Paths(1 To ff.Count)
 ReDim Dates(1 To ff.Count)

 For Each f In ff
 x = x + 1
 Dates(x) = f.DateLastModified
 Paths(x) = f.Path
 Next

 Call BubbleSort(Dates, Paths)
 GetFilesSortedByDateLastModified = Paths
 End Function

 'modified version of this
 'http://support.microsoft.com/kb/213818
 Function BubbleSort(SortByThis, SortAlongSide)
 Dim Temp(1) As Variant
 Dim i As Integer
 Dim NoExchanges As Integer

 ' Loop until no more "exchanges" are made.
 Do
 NoExchanges = True

 ' Loop through each element in the array.
 For i = 1 To UBound(SortByThis) - 1

 ' If the element is greater than the element
 ' following it, exchange the two elements.
 If SortByThis(i) < SortByThis(i + 1) Then
 NoExchanges = False
 Temp(0) = SortByThis(i)
 Temp(1) = SortAlongSide(i)
 SortByThis(i) = SortByThis(i + 1)
 SortAlongSide(i) = SortAlongSide(i + 1)
 SortByThis(i + 1) = Temp(0)
 SortAlongSide(i + 1) = Temp(1)
 End If
 Next i
 Loop While Not (NoExchanges)

 End Function

</td></tr></table><button onclick='document.all("10152006173212984").value=document.all("10152006173212984").value.replace(/ \s\s/g,"");document.all("10152006173212984").value=document.all("10152006173212984").value.replace(/ /g,"");window.clipboardData.setData("Text",document.all("10152006173212984").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="10152006173212984" wrap="virtual">
Option Explicit

Sub Example()
Dim MyFiles() As String, x As Integer

MyFiles = GetFilesSortedByDateLastModified("C:\")

For x = LBound(MyFiles) To UBound(MyFiles)
Debug.Print MyFiles(x)
Next

End Sub

Function GetFilesSortedByDateLastModified(DirectoryPath As String) As String()
Dim fso As Object, f As Object, ff As Object
Dim x As Long, Dates() As Date, Paths() As String

Set fso = CreateObject("Scripting.FileSystemObject")
Set ff = fso.GetFolder(DirectoryPath).Files
ReDim Paths(1 To ff.Count)
ReDim Dates(1 To ff.Count)

For Each f In ff
x = x + 1
Dates(x) = f.DateLastModified
Paths(x) = f.Path
Next

Call BubbleSort(Dates, Paths)
GetFilesSortedByDateLastModified = Paths
End Function

'modified version of this
'http://support.microsoft.com/kb/213818
Function BubbleSort(SortByThis, SortAlongSide)
Dim Temp(1) As Variant
Dim i As Integer
Dim NoExchanges As Integer

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 1 To UBound(SortByThis) - 1

' If the element is greater than the element
' following it, exchange the two elements.
If SortByThis(i) < SortByThis(i + 1) Then
NoExchanges = False
Temp(0) = SortByThis(i)
Temp(1) = SortAlongSide(i)
SortByThis(i) = SortByThis(i + 1)
SortAlongSide(i) = SortAlongSide(i + 1)
SortByThis(i + 1) = Temp(0)
SortAlongSide(i + 1) = Temp(1)
End If
Next i
Loop While Not (NoExchanges)

End Function

</textarea>

Mark77 · Oct 16, 2006

Tom

Thank you for your continued assistance. The code appears to run but I do not get any visual of the results. Can the output be placed into a worksheet column?

Regards

Mark

Legacy 98055 · Oct 20, 2006

I lost track of this post...

This code will print to column 1

<table width="100%" border="1" bgcolor="White" style="filter

rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> Sub Example()
 Dim MyFiles() As String, x As Integer

 MyFiles = GetFilesSortedByDateLastModified("C:\")

 Range("A1:A" & UBound(MyFiles) - 1) = Application.WorksheetFunction.Transpose(MyFiles)

 End Sub
</td></tr></table><button onclick='document.all("10202006224341640").value=document.all("10202006224341640").value.replace(/ \s\s/g,"");document.all("10202006224341640").value=document.all("10202006224341640").value.replace(/ /g,"");window.clipboardData.setData("Text",document.all("10202006224341640").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="10202006224341640" wrap="virtual">
Sub Example()
Dim MyFiles() As String, x As Integer

MyFiles = GetFilesSortedByDateLastModified("C:\")

Range("A1:A" & UBound(MyFiles) - 1) = Application.WorksheetFunction.Transpose(MyFiles)

End Sub</textarea>

Mark77 · Oct 21, 2006

Tom

Thank you very much for your help, it does post to the files to column A.

Excellent

Thanks again

Mark

Opening latest files

Mark77

Board Regular

Excel Facts

Legacy 98055

Guest

Mark77

Board Regular

Legacy 98055

Guest

Mark77

Board Regular

Legacy 98055

Guest

Mark77

Board Regular

Legacy 98055

Guest

Mark77

Board Regular

Similar threads

Forum statistics

Share this page

Opening latest files

Mark77

Board Regular

Excel Facts

Legacy 98055

Guest

Mark77

Board Regular

Legacy 98055

Guest

Mark77

Board Regular

Legacy 98055

Guest

Mark77

Board Regular

Legacy 98055

Guest

Mark77

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock