Help to change code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Les Stout - 26 Mar 2008 17:34 GMT

Hi all, Tom Hutchins helped me out with some code and i need to change
it.
The code loops through a folder and looks at the last time the file was
modified to open the latest file and i need to look at the date stamp
when the file was created to get the newest file and not the last
modified.
Any help would be very much appreciated.

Sub AAAAA()
Const FilePath = "D:\Data\"
Workbooks.Open Filename:=FindNewestFile(FilePath)
End Sub

Function FindNewestFile(FilePath As String) As String
Dim LastDate As Date, NewDate As Date
Dim LastFile As String, NewFile As String
'Check all the .XLS files in the folder. Find the
'most recent file.
LastFile$ = LCase$(Dir(FilePath$ & "*.XLS"))
LastDate = FileDateTime(FilePath$ & LastFile$)
NewFile$ = LastFile$
Do While Len(NewFile$) > 0
NewFile$ = LCase$(Dir())
If Len(NewFile$) = 0 Then Exit Do
NewDate = FileDateTime(FilePath$ & NewFile$)
If NewDate > LastDate Then
LastDate = NewDate
LastFile$ = NewFile$
End If
Loop
FindNewestFile$ = FilePath$ & LastFile$
End Function

Best regards,

Les Stout

Reply to this Message

Bob Phillips - 26 Mar 2008 18:03 GMT

Just switch the test?

Function FindNewestFile(FilePath As String) As String
Dim LastDate As Date, NewDate As Date
Dim LastFile As String, NewFile As String
'Check all the .XLS files in the folder. Find the
'most recent file.
LastFile$ = LCase$(Dir(FilePath$ & "*.XLS"))
LastDate = FileDateTime(FilePath$ & LastFile$)
NewFile$ = LastFile$
Do While Len(NewFile$) > 0
NewFile$ = LCase$(Dir())
If Len(NewFile$) = 0 Then Exit Do
NewDate = FileDateTime(FilePath$ & NewFile$)
If NewDate < LastDate Then
LastDate = NewDate
LastFile$ = NewFile$
End If
Loop
FindNewestFile$ = FilePath$ & LastFile$
End Function

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi all, Tom Hutchins helped me out with some code and i need to change
> it.
[quoted text clipped - 34 lines]
>
> *** Sent via Developersdex http://www.developersdex.com ***

Reply to this Message

Les Stout - 27 Mar 2008 10:01 GMT

Hello Bob, Perhaps i did not explain myself properly, i need to read the
"Created" date stamp and not the "modified" date stamp. Will switching
the test do that ?

I have totally different code to get the "Created" Date and i am not
sure how to intergrate it.

Your help is appreciated.

Best regards,

Les Stout

Reply to this Message

Per Jessen - 27 Mar 2008 10:13 GMT

Hi Les

This modification should do the trick.

Function FindNewestFile(FilePath As String) As String
Dim LastDate As Date, NewDate As Date
Dim LastFile As String, NewFile As String
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
'Check all the .XLS files in the folder. Find the
'file last created.
LastFile$ = LCase$(Dir(FilePath$ & "*.XLS"))

Set f = fs.getfile(FilePath$ & LastFile$)
LastDate = f.DateCreated
Set f = Nothing
NewFile$ = LastFile$
Do While Len(NewFile$) > 0
NewFile$ = LCase$(Dir())
If Len(NewFile$) = 0 Then Exit Do
Set f = fs.getfile(FilePath$ & NewFile$)
NewDate = f.DateCreated
If NewDate > LastDate Then
LastDate = NewDate
LastFile$ = NewFile$
End If
Loop
FindNewestFile$ = FilePath$ & LastFile$
End Function

Best regards,

Per

> Hi all, Tom Hutchins helped me out with some code and i need to change
> it.
[quoted text clipped - 34 lines]
>
> *** Sent via Developersdex http://www.developersdex.com ***

Reply to this Message

Les Stout - 27 Mar 2008 11:26 GMT

Thanks Par, much appreciated... :-)

Best regards,

Les Stout

Reply to this Message