• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Open latest file

Hi, i need some code to open the latest file by latest modified in a fodler location cakked

G:\XLS\Tax\Global View Reporting

I have code attached below, but that is looking to search through month folders and looks at the date, these files have no date, so the attached code fails

Thanks
Seamus
Public Sub BR()
    Dim initPath As String, Direc As String, strFile As String, strFinalFile As String
   Dim DT As Date, dteFile As Date
   Dim objFSO, objFdr, objSubFdr
   
   ' Change Path of parent directory here
   ' Don't forget the "\" after the path
   initPath = "\\ukhibmdata02\s-gdata\EVERYONE\corpactions\CAB Analyst\Corporate Actions files\UK\ON DEMAND\Reports\BR Report\"
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFdr = objFSO.getfolder(initPath)
   
   ' loop through folders, checking names
   For Each objSubFdr In objFdr.subfolders
      If IsDate(objSubFdr.Name) Then
          If DT = #12:00:00 AM# Then
            DT = CDate(objSubFdr.Name)
            Direc = objSubFdr.Name
         Else
            If CDate(objSubFdr.Name) > DT Then
               DT = CDate(objSubFdr.Name)
               Direc = objSubFdr.Name
            End If
         End If
      End If
   Next objSubFdr
   ' check we found a date
   If Len(Trim(Direc)) <> 0 Then
      ' now need to loop through the files and find the last one
      ' assumes file names are like "09022011 Merit.xls"
      ' so we want the first 8 characters converted to a date
      strFile = Dir(initPath & Direc & "\*.xls")
      If strFile <> "" Then
         Do
                     
        
            If dteFile < GetDateFromFileName(strFile) Then
               strFinalFile = strFile
            
            End If
            
             strFile = Dir
         Loop While strFile <> ""


         If Len(strFinalFile) > 0 Then
           Workbooks.Open initPath & Direc & "\" & strFinalFile

         End If
         
         
      Else
         MsgBox "No workbooks in " & initPath & Direc & "\"
      End If
   Else
      MsgBox "No date files found"
   End If
End Sub





Function GetDateFromFileName(strFile As String) As Date
   ' returns date from last 8 characters of file name
   ' assumes ddmmyyyy format
   Dim dteTemp As Date, strTemp As String
   strTemp = Right$(Replace$(strFile, ".xls", "", , , vbTextCompare), 6)
   If Len(strTemp) < 6 Then Exit Function
   strTemp = Left$(strTemp, 2) & "/" & Mid$(strTemp, 3, 2) & "/" & Mid$(strTemp, 5, 4)
   If IsDate(strTemp) Then GetDateFromFileName = CDate(strTemp)
End Function

Open in new window

0
Seamus2626
Asked:
Seamus2626
  • 2
  • 2
1 Solution
 
Kannan KManager - EngineeringCommented:
Hi,

could you please explain in more detail, so that we can achieve the result.

as per my understanding, you are expecting the file name which contains first 8 letters are the date string value. but in your question, you had asked about the latest modified filename. so it confused.

KK,
0
 
kgerbChief EngineerCommented:
Try this.  It will open the file with the latest modified date.  There is no check to make sure it is an Excel file.  If you need that verification let me know.
Sub OpenOldestFile()
Dim FSO As Object, fld As Object, FileItem As Object
Dim sFname As String, lDate As Double, Path As String
Dim wsf As WorksheetFunction

Path = "C:\Kyle\Calculation Tools\Excel"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fld = FSO.GetFolder(Path)
lDate = 0
For Each FileItem In fld.Files
    If FileItem.datelastmodified > lDate Then
        lDate = FileItem.datelastmodified
        sFname = FileItem.parentfolder.Path & "\" & FileItem.Name
    End If
Next FileItem
Debug.Print sFname
Workbooks.Open sFname
End Sub

Open in new window

Kyle
0
 
Seamus2626Author Commented:
Hi Kyle, if the code could just search for the excel that would be great

KK, i dont need any searches on filename, simply the latest modified excel.

Thanks
Seamus
0
 
kgerbChief EngineerCommented:
Here is a modified version that only looks at Excel files.  Add additional extensions as necessary on line 11.
Sub OpenOldestFile()
Dim FSO As Object, fld As Object, FileItem As Object
Dim sFname As String, lDate As Double, Path As String
Dim wsf As WorksheetFunction

Path = "C:\Kyle\Calculation Tools\Excel"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fld = FSO.GetFolder(Path)
lDate = 0
For Each FileItem In fld.Files
    If InStr(1, "XLS, XLSX, XLSM", UCase(Mid(FileItem.Name, InStrRev(FileItem.Name, ".", -1) + 1))) > 0 Then
        If FileItem.datelastmodified > lDate Then
            lDate = FileItem.datelastmodified
            sFname = FileItem.parentfolder.Path & "\" & FileItem.Name
        End If
    End If
Next FileItem
Debug.Print sFname
Workbooks.Open sFname
End Sub

Open in new window

Kyle
0
 
Seamus2626Author Commented:
Thanks Kyle!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now