Opening file with date tags

[JohnUK]

Many thanks to Bob Phillips and Ivan for their help with picking up a file
with a date tag using code. (For some reason the listing has now disappeared)
I need to go a step further, I need the latest created file to be opened.
The code below shows how far I Have got:
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
sfilename = "My File*.xls"
If Len(Dir(sPath & "\" & sfilename)) = 0 Then
MsgBox "No file"
Else
sfilename = sPath & "\" & Dir(sPath & "\" & sfilename)
Set wb = Workbooks.Open(sfilename)
End If
All the workbooks start with 'My File' and end with different dates & times,
and at the moment it only picks up the oldest file.
Again - any help greatly appreciated
John

[colofnature]

Replace the line

Set wb = Workbooks.Open(sfilename)

with the following:

sfilename = sPath & "\" & Dir(sPath & "\" & sfilename)
NewestFileName = sfilename
FileDate = FileDateTime(sfilename)
Do While Len(sfilename) > Len(sPath & "\")
If FileDateTime(sfilename) > FileDate Then
NewestFileName = sfilename
FileDate = FileDateTime(sfilename)
End If
sfilename = sPath & "\" & Dir()
Loop
Set wb = Workbooks.Open(NewestFileName)

I haven't tested it, but it ought to work...
Col

[JohnUK]

Great it works

Many thanks colofnature

Also thanks to Tom - I will try yours later

Must leave now.........

"" wrote:

>
> Replace the line
>
> Set wb = Workbooks.Open(sfilename)
>
> with the following:
>
> sfilename = sPath & "\" & Dir(sPath & "\" & sfilename)
> NewestFileName = sfilename
> FileDate = FileDateTime(sfilename)
> Do While Len(sfilename) > Len(sPath & "\")
> If FileDateTime(sfilename) > FileDate Then
> NewestFileName = sfilename
> FileDate = FileDateTime(sfilename)
> End If
> sfilename = sPath & "\" & Dir()
> Loop
> Set wb = Workbooks.Open(NewestFileName)
>
> I haven't tested it, but it ought to work...
> Col
>
>
> --
> colofnature
> ------------------------------------------------------------------------
> colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
> View this thread: http://www.excelforum.com/showthread...hreadid=544793
>
>

[Tom Ogilvy]

What value does sFile have?

what do your filenames look like?

Assume the filename looks like Myfile20061015.xls

then you could do
Dim dtMax as Date, dt as Date
Dim s as String, s1 as String
Dim sDt as String, sPath as String
dtMax = 0
sPath = "C:\MyFiles\"
s = Dir(sPath & "Myfile*.xls")
do while s<> ""
sDt = Mid(s,7,8)
dt = dateSerial(clng(left(sDt,1,4)),clng(mid(s,5,2)),clng(right(s,2)))
if dt > maxDt then
s1 = s
maxDt = dt
end if
s = dir()
Loop
workbooks.open sPath & s

if your not making the determination by the date in the filename, then look
at the filedatetime function.

--
Regards,
Tom Ogilvy


"JohnUK" wrote:

> Many thanks to Bob Phillips and Ivan for their help with picking up a file
> with a date tag using code. (For some reason the listing has now disappeared)
> I need to go a step further, I need the latest created file to be opened.
> The code below shows how far I Have got:
> Dim wb As Workbook
> sPath = Application.DefaultFilePath & "\" & sFile
> sfilename = "My File*.xls"
> If Len(Dir(sPath & "\" & sfilename)) = 0 Then
> MsgBox "No file"
> Else
> sfilename = sPath & "\" & Dir(sPath & "\" & sfilename)
> Set wb = Workbooks.Open(sfilename)
> End If
> All the workbooks start with 'My File' and end with different dates & times,
> and at the moment it only picks up the oldest file.
> Again - any help greatly appreciated
> John

[JohnUK]

Hi Colofnature - thanks for your help

I have now got:

Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
sfilename = "Front Page*.xls"
If Len(Dir(sPath & "\" & sfilename)) = 0 Then
MsgBox "No file"
Else
sfilename = sPath & "\" & Dir(sPath & "\" & sfilename)
'Set wb = Workbooks.Open(sfilename)
NewestFileName = sfilename
FileDate = FileDateTime(sfilename)
Do While sfilename <> ""
sfilename = sPath & "\" & Dir()
If FileDateTime(sfilename) > FileDate Then
NewestFileName = sfilename
FileDate = FileDateTime(sfilename)
End If
Loop
Set wb = Workbooks.Open(NewestFileName)
End If
End Sub

But for some reason it gets stuck on:

If FileDateTime(sfilename) > FileDate Then

John

"colofnature" wrote:

>
> Replace the line
>
> Set wb = Workbooks.Open(sfilename)
>
> with the following:
>
> NewestFileName = sfilename
> FileDate = FileDateTime(sfilename)
> Do while sfilename <> ""
> sfilename = sPath & "\" & dir()
> if FileDateTime(sfilename) > FileDate then
> NewestFileName = sfilename
> FileDate = FileDateTime(sfilename)
> end if
> loop
> Set wb = Workbooks.Open(NewestFileName)
>
>
> --
> colofnature
> ------------------------------------------------------------------------
> colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
> View this thread: http://www.excelforum.com/showthread...hreadid=544793
>
>