Open latest file

[Bigfoot17]

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?

 

[Nick]

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.

 

[Tom Hutchins]

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]

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.

 

[Bigfoot17]

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.