How can I search and select a specific file by file_name using VBA?

[Apollo6]

I have a directory that contains archive files. The name of the file are the current_date.ptx. The archive processes a week's worth of data and writes daily files as follows, all dated with a LastModified_Date being the same:

Examples:
File_Names LastModified
05142002.ptx 05/15/2002 11:00:00am
05132002.ptx 05/15/2002 11:00:00am
05122002.ptx 05/15/2002 11:00:00am
etc...

I have written a macro that can go to the specific directory and pull a specific file (hard-coded) then continue on with formatting the file and save it as a flat-file in another directory.

What I want is to be able to logically take the most recent record only compared with today's date. Which I believe should/could be done through the file_name. Possibly converting the file_name into a regular date and comparing it to "Date".

Can this be done in VBA?

 

[kevinclark]

Is it possible for you to use the CreationDate

Sub Sample()
'First need to set a reference to the Microsoft
'Scripting Runtime
Dim FSO
Dim FLDR
Dim FC
Dim FO
Set FSO = CreateObject("Scripting.FileSystemObject&quot
Set FLDR = FSO.GetFolder("C:\Sample\&quot
Set FC = FLDR.Files
For Each FO In FC
If Format(FO.DateCreated, "MM/DD/YYYY&quot = Format(Now, "MM/DD/YYYY&quot Then
MsgBox FO.Name
End If
Next
End Sub

 

[rmikesmith]

Apollo6,

Although Kevin's solution seems simpler to me, I'm posting my take on this mainly because I've already gen'd it up.

Kevin: I know nothing about the scripting runtime and the FSO object, even though I've seen it used in these forums several times. How can I learn a little more about it? Thanks.

Here's my code, which looks at the archive filenames and returns the most recent. You can copy and paste into a Code Module and test it out.

Regards,
M. Smith

Option Explicit
Option Base 1


Const ArchivePath As String = "F:\Archive\"  'Set to your path

Sub Test()

  MsgBox "Most recent archive file is " & GetMostRecent(ArchivePath), _
    vbInformation + vbOKOnly, "Archive Files"
    
End Sub


Function GetMostRecent(ByVal Path As String) As String
Dim FNames() As String
Dim FName As String
Dim FCount As Long


FCount = 0
FName = Dir(Path & "*.ptx", vbNormal)
If FName <> &quot;&quot; Then
  FCount = FCount + 1
  ReDim FNames(FCount)
  FNames(FCount) = FName
End If
Do While FName <> &quot;&quot;
  FName = Dir
  If FName <> &quot;&quot; Then
    FCount = FCount + 1
    ReDim Preserve FNames(FCount)
    FNames(FCount) = FName
  End If
Loop
If FCount = 0 Then
  GetMostRecent = &quot;No Files Found&quot;
Else
  SortNames FNames
  GetMostRecent = FNames(UBound(FNames))
End If
End Function


Sub SortNames(ByRef list() As String)
'   Sorts a string array using bubble sort algorithm
    Dim First As Integer, Last As Long
    Dim i As Long, j As Long
    Dim Temp As String
    
    First = LBound(list)
    Last = UBound(list)
    For i = First To Last - 1
        For j = i + 1 To Last
            If list(i) > list(j) Then
                Temp = list(j)
                list(j) = list(i)
                list(i) = Temp
            End If
        Next j
    Next i
End Sub

 

[kevinclark]

Hi rmikesmith

The one thing I enjoy most about VB is its versatility, I am forever being impressed by the many different ways to accomplish the same thing.

The FileSystemObject is component that can be used to access system files. I find it handy for retrieving the file properties like date created, size, last modified date or for renaming, creating, moving, copying or deleting text files. It has its limitations though, as it can not sort files (need to create your own algorithm) and does not read lines from files in binary format.
I have found the Microsoft Help files to be the best source of examples on how to use the component.

 

[rmikesmith]

Kevin, Thanks for the info!

Mike

 

[Apollo6]

Thanks for the help... I was actually reading about FileSystemObject over the weekend.

Regards