Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to change code
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to change code
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 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Les Stout" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to change code
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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to change code
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 "Les Stout" skrev i meddelelsen ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to change code
Thanks Par, much appreciated... :-)
Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to change code in a sheet and workbook module | Excel Programming | |||
Change code with code? | Excel Programming | |||
Change code by code. Can you do that? | Excel Programming | |||
Can I use code/macro to change code/macro in an existing file? | Excel Programming | |||
Code Conflicts With Worksheet Change Code | Excel Programming |