Opening latest files

Mark77

Board Regular
Joined
Aug 27, 2004
Messages
66
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
L

Legacy 98055

Guest
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:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> MyFiles() <font color="#0000A0">As</font> String, x <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>

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

       <font color="#0000A0">For</font> x = 0 <font color="#0000A0">To</font> UBound(MyFiles)
           Debug.Print MyFiles(x)
       <font color="#0000A0">Next</font>

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#008000">'contains references to mscorlib and Microsoft Scripting Runtime</font>
  <font color="#0000A0">Function</font> GetFilesByDateLastModified(DirectoryPath <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> String()
       <font color="#0000A0">Dim</font> al <font color="#0000A0">As</font> <font color="#0000A0">New</font> mscorlib.ArrayList, fso <font color="#0000A0">As</font> <font color="#0000A0">New</font> Scripting.FileSystemObject, f <font color="#0000A0">As</font> Scripting.File
       <font color="#0000A0">Dim</font> x <font color="#0000A0">As</font> Long, TempString() <font color="#0000A0">As</font> <font color="#0000A0">String</font>

       <font color="#0000A0">For</font> <font color="#0000A0">Each</font> f <font color="#0000A0">In</font> fso.GetFolder(DirectoryPath).Files
           al.Add f.DateLastModified & "|" & f.Path
       <font color="#0000A0">Next</font>

       al.Sort
       al.Reverse
       <font color="#0000A0">ReDim</font> TempString(0 <font color="#0000A0">To</font> al.Count - 1)
       <font color="#0000A0">For</font> x = 0 <font color="#0000A0">To</font> al.Count - 1
           TempString(x) = Split(al(x), "|")(1)
       <font color="#0000A0">Next</font>

       GetFilesByDateLastModified = TempString
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>

</FONT></td></tr></table><button onclick='document.all("10152006112133218").value=document.all("10152006112133218").value.replace(/<br \/>\s\s/g,"");document.all("10152006112133218").value=document.all("10152006112133218").value.replace(/<br \/>/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

Board Regular
Joined
Aug 27, 2004
Messages
66
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
 
L

Legacy 98055

Guest
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

Board Regular
Joined
Aug 27, 2004
Messages
66

ADVERTISEMENT

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?
 
L

Legacy 98055

Guest
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:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> MyFiles() <font color="#0000A0">As</font> String, x <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>

       MyFiles = GetFilesSortedByDateLastModified("C:\")

       <font color="#0000A0">For</font> x = LBound(MyFiles) <font color="#0000A0">To</font> UBound(MyFiles)
           Debug.Print MyFiles(x)
       <font color="#0000A0">Next</font>

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Function</font> GetFilesSortedByDateLastModified(DirectoryPath <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> String()
       <font color="#0000A0">Dim</font> fso <font color="#0000A0">As</font> Object, f <font color="#0000A0">As</font> Object, ff <font color="#0000A0">As</font> <font color="#0000A0">Object</font>
       <font color="#0000A0">Dim</font> x <font color="#0000A0">As</font> Long, Dates() <font color="#0000A0">As</font> Date, Paths() <font color="#0000A0">As</font> <font color="#0000A0">String</font>

       <font color="#0000A0">Set</font> fso = CreateObject("Scripting.FileSystemObject")
       <font color="#0000A0">Set</font> ff = fso.GetFolder(DirectoryPath).Files
       <font color="#0000A0">ReDim</font> Paths(1 <font color="#0000A0">To</font> ff.Count)
       <font color="#0000A0">ReDim</font> Dates(1 <font color="#0000A0">To</font> ff.Count)

       <font color="#0000A0">For</font> <font color="#0000A0">Each</font> f <font color="#0000A0">In</font> ff
           x = x + 1
           Dates(x) = f.DateLastModified
           Paths(x) = f.Path
       <font color="#0000A0">Next</font>

       <font color="#0000A0">Call</font> BubbleSort(Dates, Paths)
       GetFilesSortedByDateLastModified = Paths
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>

  <font color="#008000">'modified version of this</font>
  <font color="#008000">'http://support.microsoft.com/kb/213818</font>
  <font color="#0000A0">Function</font> BubbleSort(SortByThis, SortAlongSide)
       <font color="#0000A0">Dim</font> Temp(1) <font color="#0000A0">As</font> <font color="#0000A0">Variant</font>
       <font color="#0000A0">Dim</font> i <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>
       <font color="#0000A0">Dim</font> NoExchanges <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>

      <font color="#008000"> ' Loop until no more "exchanges" are made.</font>
       <font color="#0000A0">Do</font>
           NoExchanges = <font color="#0000A0">True</font>

          <font color="#008000"> ' Loop through each element in the array.</font>
           <font color="#0000A0">For</font> i = 1 <font color="#0000A0">To</font> UBound(SortByThis) - 1

              <font color="#008000"> ' If the element is greater than the element</font>
              <font color="#008000"> ' following it, exchange the two elements.</font>
               <font color="#0000A0">If</font> SortByThis(i) < SortByThis(i + 1) <font color="#0000A0">Then</font>
                   NoExchanges = <font color="#0000A0">False</font>
                   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)
               <font color="#0000A0">End</font> <font color="#0000A0">If</font>
           <font color="#0000A0">Next</font> i
       <font color="#0000A0">Loop</font> <font color="#0000A0">While</font> <font color="#0000A0">Not</font> (NoExchanges)

  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>



</FONT></td></tr></table><button onclick='document.all("10152006173212984").value=document.all("10152006173212984").value.replace(/<br \/>\s\s/g,"");document.all("10152006173212984").value=document.all("10152006173212984").value.replace(/<br \/>/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

Board Regular
Joined
Aug 27, 2004
Messages
66

ADVERTISEMENT

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
 
L

Legacy 98055

Guest
I lost track of this post...

This code will print to column 1

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> MyFiles() <font color="#0000A0">As</font> String, x <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>

       MyFiles = GetFilesSortedByDateLastModified("C:\")


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

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("10202006224341640").value=document.all("10202006224341640").value.replace(/<br \/>\s\s/g,"");document.all("10202006224341640").value=document.all("10202006224341640").value.replace(/<br \/>/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

Board Regular
Joined
Aug 27, 2004
Messages
66
Tom

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

Excellent

Thanks again

Mark
 

Forum statistics

Threads
1,141,596
Messages
5,707,300
Members
421,502
Latest member
PULBAG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top