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?

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.

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?

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?

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?