Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open latest file
I usually don't do as well with questions asked on Friday, but I need to
anyway. I have one user adding files to a folder using a common name and date scheme: "File 012909.xls" "File 020509.xls" "File 021209.xls" "File 021909.xls" etc. What I have been setting up is a control panel for a user so they do not need to mess with lengthy pathnames etc. I would like to be able to click a button on the control panel to open the latest version of "File" in the folder. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open latest file
On Feb 20, 3:13*pm, Bigfoot17
wrote: I usually don't do as well with questions asked on Friday, but I need to anyway. I have one user adding files to a folder using a common name and date scheme: "File 012909.xls" "File 020509.xls" "File 021209.xls" "File 021909.xls" etc. What I have been setting up is a control panel for a user so they do not need to mess with lengthy pathnames etc. *I would like to be able to click a button on the control panel to open the latest version of "File" in the folder. *Any suggestions? the easiest suggestion, is choose a better naming format, ie yyyymmdd file.xls then just sort the folder by name. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open latest file
Maybe something like
Sub AAAAA() 'Call FindNewestFile. Tell it the path & the file name pattern. MsgBox FindNewestFile("D:\Tom's Files\", "File*.xls") End Sub Private Function FindNewestFile(MyPath As String, MyFile As String) As String Dim LastDate As Date, NewDate As Date Dim LastFile As String, NewFile As String LastFile$ = LCase$(Dir(MyPath$ & MyFile$)) LastDate = FileDateTime(MyPath$ & LastFile$) NewFile$ = LastFile$ Do While Len(NewFile$) 0 NewFile$ = LCase$(Dir()) If Len(NewFile$) = 0 Then Exit Do NewDate = FileDateTime(MyPath$ & NewFile$) If NewDate LastDate Then LastDate = NewDate LastFile$ = NewFile$ End If Loop FindNewestFile = LastFile$ End Function Hope this helps, Hutch "Bigfoot17" wrote: I usually don't do as well with questions asked on Friday, but I need to anyway. I have one user adding files to a folder using a common name and date scheme: "File 012909.xls" "File 020509.xls" "File 021209.xls" "File 021909.xls" etc. What I have been setting up is a control panel for a user so they do not need to mess with lengthy pathnames etc. I would like to be able to click a button on the control panel to open the latest version of "File" in the folder. Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open latest file
I find your response interesting and have been studying at and am attempting
to adopt it for my usage. While the code runs correctly as written, I have run into a problem. My goal is to OPEN the returned filename and while I have tried several things I am not able to get the file to open. I keep getting a 1004 error. Any further assistance is appreciated. "Tom Hutchins" wrote: Maybe something like Sub AAAAA() 'Call FindNewestFile. Tell it the path & the file name pattern. MsgBox FindNewestFile("D:\Tom's Files\", "File*.xls") End Sub Private Function FindNewestFile(MyPath As String, MyFile As String) As String Dim LastDate As Date, NewDate As Date Dim LastFile As String, NewFile As String LastFile$ = LCase$(Dir(MyPath$ & MyFile$)) LastDate = FileDateTime(MyPath$ & LastFile$) NewFile$ = LastFile$ Do While Len(NewFile$) 0 NewFile$ = LCase$(Dir()) If Len(NewFile$) = 0 Then Exit Do NewDate = FileDateTime(MyPath$ & NewFile$) If NewDate LastDate Then LastDate = NewDate LastFile$ = NewFile$ End If Loop FindNewestFile = LastFile$ End Function Hope this helps, Hutch "Bigfoot17" wrote: I usually don't do as well with questions asked on Friday, but I need to anyway. I have one user adding files to a folder using a common name and date scheme: "File 012909.xls" "File 020509.xls" "File 021209.xls" "File 021909.xls" etc. What I have been setting up is a control panel for a user so they do not need to mess with lengthy pathnames etc. I would like to be able to click a button on the control panel to open the latest version of "File" in the folder. Any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open latest file
Nevermind, in searching for my post I found where you replied to another user
facing a very similar problema nd in that post you suggested: Workbooks.Open Filename:=FindNewestFile(FilePath) I usually spend considerable amount of time researching my problem in the group before posing a question because other users usually have similar questions. I don't know how I missed the 3/08 post, but I am grateful for the response. "Tom Hutchins" wrote: Maybe something like Sub AAAAA() 'Call FindNewestFile. Tell it the path & the file name pattern. MsgBox FindNewestFile("D:\Tom's Files\", "File*.xls") End Sub Private Function FindNewestFile(MyPath As String, MyFile As String) As String Dim LastDate As Date, NewDate As Date Dim LastFile As String, NewFile As String LastFile$ = LCase$(Dir(MyPath$ & MyFile$)) LastDate = FileDateTime(MyPath$ & LastFile$) NewFile$ = LastFile$ Do While Len(NewFile$) 0 NewFile$ = LCase$(Dir()) If Len(NewFile$) = 0 Then Exit Do NewDate = FileDateTime(MyPath$ & NewFile$) If NewDate LastDate Then LastDate = NewDate LastFile$ = NewFile$ End If Loop FindNewestFile = LastFile$ End Function Hope this helps, Hutch "Bigfoot17" wrote: I usually don't do as well with questions asked on Friday, but I need to anyway. I have one user adding files to a folder using a common name and date scheme: "File 012909.xls" "File 020509.xls" "File 021209.xls" "File 021909.xls" etc. What I have been setting up is a control panel for a user so they do not need to mess with lengthy pathnames etc. I would like to be able to click a button on the control panel to open the latest version of "File" in the folder. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Latest Taxation Books available at jain book depot LATEST BOOKRELEASES | Excel Worksheet Functions | |||
Find latest file in folder & Open | Excel Programming | |||
FileSearch to locate the latest (last saved) file | Excel Programming | |||
macro to open latest file | Excel Programming | |||
inserting latest picture file | Excel Programming |