Last 2 files joined together

[Michael Smith]

I have a file "C:/temp"
I have files in this folder with only 1 worksheet in each workbook.
There are many file in this folder, however I want my sub to take the
last 2 files to have been saved to this folder and copy them to be 2
worksheets in a separate workbook.
Possible? TIA - Mike

*** Sent via Developersdex http://www.developersdex.com ***

[Toppers]

Hi,


See VB "FileDateTime" function which will give you the info to find latest
modified date ...


Example from VB Help:

Dim MyStamp
' Assume TESTFILE was last modified on February 12, 1993 at 4:35:47 PM.
' Assume English/U.S. locale settings.
MyStamp = FileDateTime("TESTFILE") ' Returns "2/12/93 4:35:47 PM".



"Michael Smith" wrote:

> I have a file "C:/temp"
> I have files in this folder with only 1 worksheet in each workbook.
> There are many file in this folder, however I want my sub to take the
> last 2 files to have been saved to this folder and copy them to be 2
> worksheets in a separate workbook.
> Possible? TIA - Mike
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

[Michael Smith]

Great that helps....how would i then compile a list of all times and
file names that are in a folder?

Would this be a For Each loop?



*** Sent via Developersdex http://www.developersdex.com ***

[Toppers]

Mike,

Try this: it list the files and date/times in columns A/B and
sorts them in descending order so that rows 1 & 2 are the latest.

Place this code in a general module in your workbook - it uses first
worksheet (normally Sheet1) to store the results. Change the "With
Worksheet.." statement to suit AND the .Lookin statement .

Execute the FindFile macro.

HTH

Sub FindFile()

Dim FNames() As String, FTimes() As Date

Set fs = Application.FileSearch

With fs
filescount = 0

.LookIn = "C:\Documents and Settings\John\My Documents\My Excel" '<===
CHANGE
.Filename = "*.xls"
.Execute

If .FoundFiles.Count > 0 Then

For i = 1 To .FoundFiles.Count
docname = FileNameOnly(.FoundFiles(i))
filescount = filescount + 1
ReDim Preserve FNames(filescount)
ReDim Preserve FTimes(filescount)
FNames(filescount) = docname
FTimes(filescount) = FileDateTime(.FoundFiles(i))
Next i

End If
End With
With Worksheets(1)
For i = 1 To filescount
.Cells(i, 1) = FTimes(i)
.Cells(i, 2) = FNames(i)
Next
.Range("a1:b" & filescount).Sort Key1:=.Range("A1"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
Function FileNameOnly(pname) As String
' Returns the filename from a path/filename string
Dim i As Integer, length As Integer, temp As String
length = Len(pname)
temp = ""
For i = length To 1 Step -1
If Mid(pname, i, 1) = Application.PathSeparator Then
FileNameOnly = temp
Exit Function
End If
temp = Mid(pname, i, 1) & temp
Next i
FileNameOnly = pname
End Function


"Michael Smith" wrote:

> Great that helps....how would i then compile a list of all times and
> file names that are in a folder?
>
> Would this be a For Each loop?
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>