Trouble with getting latest file

[Guest]

Hi,

I have a problem getting the latest file of a series of files created. Each
week a number of files (can change each week) are created. I am unable to
change the naming convention. They are all in a path of
\\wpwss05\GrpData\XFERDATA\Reports\.


The filenames are:

SMSR_PLN_GEN_POL_XLS09092006_1_200609110531.txt
SMSR_PLN_GEN_POL_XLS09092006_2_200609110531.txt
SMSR_PLN_GEN_POL_XLS09092006_3_200609110531.txt
SMSR_PLN_GEN_POL_XLS09092006_4_200609110532.txt
SMSR_PLN_GEN_POL_XLS09092006_5_200609110532.txt


Looking in Windows Explorer the date modified for the above files are:

11/09/2006 5:32 AM
11/09/2006 5:32 AM
11/09/2006 5:32 AM
11/09/2006 5:33 AM
11/09/2006 5:33 AM

I am using the following code:

Private Sub Btn_Import_Files_Click()

Dim iResponse As Integer
Dim l_sFilename As String
l_sFilename = ""
SysCmd acSysCmdSetStatus, "Importing Data Files"
l_sFilename = getlatestfilename("SMSR_PLN_GEN_POL_XLS")
MsgBox ("The file selected was: " & l_sFilename)

DoCmd.Hourglass False

SysCmd acSysCmdSetStatus, " "

DoCmd.SetWarnings True
MsgBox "File(s) imported.", vbOKOnly, "File Import"


End Sub


Private Function getlatestfilename(p_sName As String)

With Application.FileSearch
.NewSearch
.LookIn = "\\wpwss05\GrpData\XFERDATA\Reports\"
.SearchSubFolders = False
.FileType = MsoFileType.msoFileTypeAllFiles
.PropertyTests.Add "File name", msoConditionEndsWith, ".txt"

.FileName = p_sName & "???????????????????????"
If .Execute(MsoSortBy.msoSortByLastModified,
MsoSortOrder.msoSortOrderDescending) > 0 Then
getlatestfilename = .FoundFiles(1)
Else
getlatestfilename = Empty
End If
End With

End Function

My problem is when I produce the results in the message box it ALWAYS gives
me the FIRST file (SMSR_PLN_GEN_POL_XLS09092006_1_200609110531.txt) as the
most recent. I'm pretty sure it should be
SMSR_PLN_GEN_POL_XLS09092006_5_200609110531.txt.

Any ideas?
Andrew 120906

 

[John Nurick]

Hi Andrew,

I seldom if ever use the FileSearch object and don't entirely trust it.
For the present task, I would

1) Make sure I understand the naming convention. First, is it always
certain that in an alphanumeric sort of filenames, a later file will
always come after an earlier one? I'm wondering what comes after
SMSR_PLN_GEN_POL_XLS09092006_9_200609150532.txt
?
Is it
SMSR_PLN_GEN_POL_XLS09092006_10_200609120532.txt
or
SMSR_PLN_GEN_POL_XLS09092006_0_200609120532.txt
? Either of these will sort before its predecessor.

Likewise, if the element 092006 refers to September 2006 and is liable
to change to - let's say 022007 for February 2007, this will foil the
alphanumeric sort.

So on the evidence so far you need to ignore everything but the final
timestamp.

2) Next I'd just use Dir() to get the filenames and a few lines of VBA
to find the latest, e.g. this air code

Dim FolderName As String
Dim FileName As String
Dim LatestDateStamp As String
Dim LatestName As String
Dim CurrentDateStamp As String

FolderName = "\\wpwss05\GrpData\XFERDATA\Reports\"
FileName = "*.txt" 'modify this if there's a chance of unrelated
'txt files in the folder

FileName = Dir() 'get first name from folder

Do While Len(strFileName) > 0
CurrentDateStamp = Mid(FileName, Len(FileName) - 15, 12)
If CurrentDateStamp > LatestDateStamp Then
LatestDateStamp = CurrentDateStamp
LatestName = FileName
End If
FileName = Dir() 'Get next filename
Loop

 

[Guest]

LatestNameHi John,

Thanks for your assistance. Unfortunately it didn't work, I get an
'Invalid procedure call or argument' at the The_Filename = Dir()" line. I
THINK I can see where it's heading but I'm not quite sure how to repair this.

I also need to check that I will get the filename of the file with the
latest modified timestamp. It is any file in the directory that begins with
SMSR_PLN_GEN_POL_XLS.

Also, it required me to change field lilename to The_Filename (reserved word
I'm tipping).

Dim FolderName As String
Dim The_FileName As String
Dim LatestDateStamp As String
Dim LatestName As String
Dim CurrentDateStamp As String
Dim strFileName As String

FolderName = "\\wpwss05\GrpData\XFERDATA\Reports\"
The_FileName = "SMSR_PLN_GEN_POL_XLS???????????????????????.txt" 'modify
this if there's a chance of unrelated
'txt files in the folder

The_FileName = Dir() 'get first name from folder

Do While Len(strFileName) > 0
CurrentDateStamp = Mid(The_FileName, Len(The_FileName) - 15, 12)
If CurrentDateStamp > LatestDateStamp Then
LatestDateStamp = CurrentDateStamp
LatestName = The_FileName
End If
The_FileName = Dir() 'Get next filename
Loop

End Sub

 

[John Nurick]

The_FileName = Dir() 'get first name from folder

In the first call to Dir() you have to pass the filespec, e.g.
The_FileName = Dir(FolderName & The_FileName)