Opening file with date tags

[Guest]

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:

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]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Great it works

Many thanks colofnature

Also thanks to Tom - I will try yours later

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