Opening file with date tags
 

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

Opening file with date tags
 

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

Opening file with date tags
 

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

Opening file with date tags
 

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)),cl ng(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

Opening file with date tags
 

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

Opening file with date tags
 

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