Hi,

I use this code to find the last file.
Set fso = New Scripting.FileSystemObject
For Each ThisFile In fso.GetFolder(sICAPPathName).Files
If ThisFile.Name Like "ICAP_FPC*.*" Then
Call GetICAPDate
If LatestFile Is Nothing And dThisFilesDate < dLastFileDate Then
Set LatestFile = ThisFile
dLastUpdated = dThisFilesDate
ElseIf dThisFilesDate dLastUpdated _
And dThisFilesDate < dLastFileDate Then
Set LatestFile = ThisFile
dLastUpdated = dThisFilesDate
End If
End If
Next

I have to use a sub routine to interpret the date as the date can be in
several different formats. I would like to read the files in update order as
this gets them in file name order, how do I change it to do that.

Thanks MarkS

If you want just the most recent file then...

Sub LatestFile()
'Jim Cone - San Francisco, USA - June 2005
'Displays the latest file name in the strPath folder.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim varDate As Variant

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office\Library"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified varDate Then
varDate = objFile.DateLastModified
strName = objFile.Name
End If
Next 'objFile

' Display file name in message box.
MsgBox strName & " - is latest file - " & varDate

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"MarkS"

wrote in message
Hi,
I use this code to find the last file.
Set fso = New Scripting.FileSystemObject
For Each ThisFile In fso.GetFolder(sICAPPathName).Files
If ThisFile.Name Like "ICAP_FPC*.*" Then
Call GetICAPDate
If LatestFile Is Nothing And dThisFilesDate < dLastFileDate Then
Set LatestFile = ThisFile
dLastUpdated = dThisFilesDate
ElseIf dThisFilesDate dLastUpdated _
And dThisFilesDate < dLastFileDate Then
Set LatestFile = ThisFile
dLastUpdated = dThisFilesDate
End If
End If
Next

I have to use a sub routine to interpret the date as the date can be in
several different formats. I would like to read the files in update order as
this gets them in file name order, how do I change it to do that.
Thanks MarkS

I tend not to use the FSO, but VBA's Dir (and IIRC even the underlying API
functions) does not specify an order in which files are returned. It may
appear alphabetical (due the the OS), but it up to you to get a filenames
that match your criteria, then sort them as desired.
VBA's Dir and FileDateTime will be useful. See below for a demo, only
quickly tested.

Private Type FileData
FileName As String
FileModTime As Date
End Type

Private Sub CommandButton2_Click()
Dim FileNames() As String
Dim i As Long

'Change as required
Const FolderToCheck As String = "C:\"
Const PatternToMatch As String = "*.*"
Const NumberToMatch As Long = 3

FileNames = GetNewestFile(FolderToCheck, PatternToMatch, NumberToMatch)

'Dirty way of checking for uninitialised array
If Not (Not FileNames) Then
For i = LBound(FileNames) To UBound(FileNames)
'Do something
Debug.Print FileNames(i)
Next
End If

End Sub

Private Function GetNewestFile(ByVal Folder As String, ByVal SearchPattern
As String, NewestCount As Long) As String()
Dim AllFiles() As FileData
Dim FileCount As Long
Dim FileName As String
Dim i As Long
Dim TempArray() As String

'To avoid too many ReDims
'Adjust depending on expect number of files
Const REDIMCHUNK As Long = 10

ReDim AllFiles(1 To REDIMCHUNK)

'Make sure we have the correct path structure
If Right(Folder, 1) < "\" Then Folder = Folder & "\"

FileName = Dir(Folder & SearchPattern)

Do Until FileName = ""
'Debug.Print FileName
FileCount = FileCount + 1
AllFiles(FileCount).FileName = FileName
AllFiles(FileCount).FileModTime = FileDateTime(Folder & FileName)
'Allocate more elements as required
If FileCount = UBound(AllFiles) Then ReDim Preserve AllFiles(1 To
UBound(AllFiles) + REDIMCHUNK)
FileName = Dir()
Loop

'Sort the array elements

'<SelectionSort code taken from http://vb-helper.com/tut1.htm
'With UDT suggestion from http://www.devx.com/vb/Article/17313/1954?pf=true
Dim j As Integer
Dim best_value As Long
Dim best_j As Integer

For i = LBound(AllFiles) To UBound(AllFiles) - 1
best_value = AllFiles(i).FileModTime
best_j = i
For j = i + 1 To UBound(AllFiles)
If AllFiles(j).FileModTime < best_value Then
best_value = AllFiles(j).FileModTime
best_j = j
End If
Next j
AllFiles(best_j).FileModTime = AllFiles(i).FileModTime
AllFiles(i).FileModTime = best_value
Next i
'</SelectionSort code taken from http://vb-helper.com/tut1.htm
'With UDT suggestion from http://www.devx.com/vb/Article/17313/1954?pf=true

'If more files matched the criteria than we requested
If UBound(AllFiles) NewestCount Then
ReDim TempArray(1 To NewestCount)
Else
'Otherwise just return all that we found
ReDim TempArray(1 To UBound(AllFiles))
End If

'Fill with the file name, drop the date part
For i = 1 To NewestCount
TempArray(i) = AllFiles(i).FileName
Next

GetNewestFile = TempArray

End Function

NickHK


"MarkS" wrote in message
...
HI Jim,
unfortunatly I need to get the last three files, and this method sometimes
gets confused because it doesn't get the files in update order.


"Jim Cone" wrote:


If you want just the most recent file then...

Sub LatestFile()
'Jim Cone - San Francisco, USA - June 2005
'Displays the latest file name in the strPath folder.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim varDate As Variant

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office\Library"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified varDate Then
varDate = objFile.DateLastModified
strName = objFile.Name
End If
Next 'objFile

' Display file name in message box.
MsgBox strName & " - is latest file - " & varDate

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"MarkS"

wrote in message
Hi,
I use this code to find the last file.
Set fso = New Scripting.FileSystemObject
For Each ThisFile In fso.GetFolder(sICAPPathName).Files
If ThisFile.Name Like "ICAP_FPC*.*" Then
Call GetICAPDate
If LatestFile Is Nothing And dThisFilesDate < dLastFileDate Then
Set LatestFile = ThisFile
dLastUpdated = dThisFilesDate
ElseIf dThisFilesDate dLastUpdated _
And dThisFilesDate < dLastFileDate Then
Set LatestFile = ThisFile
dLastUpdated = dThisFilesDate
End If
End If
Next

I have to use a sub routine to interpret the date as the date can be in
several different formats. I would like to read the files in update
order as
this gets them in file name order, how do I change it to do that.
Thanks MarkS

Thanks that does what I want a fixes another problem I was having