VBA Find Latest Folder then largest file.

**WAW** · 11-24-2010, 06:52 AM

HI All

I would like to write a procedure which will automatically find and open a file.

The file folder is alway located in c:\backup\

I would like to find the latest folder in the above directory then look in that folder and open the largest file containied within.

Thanks in advance for your help.

**WAW** · 11-24-2010, 08:30 AM

I note in other threads the use of fileSearch however, notice this does not seem to work in Excel 2007 which I am using.

**WAW** · 11-30-2010, 09:56 AM

Any Ideas on this one anyone?

**watersev** · 11-30-2010, 10:34 AM

to find largest file (all code is taken from Help in VB):

Sub Folder_and_file()
Dim fs, f, f1, fc, s, folderspec, temp As Date, myname As String
Set fs = CreateObject("Scripting.FileSystemObject")
folderspec = "c:\backup"
Set f = fs.GetFolder(folderspec): Set fc = f.subfolders
For Each f1 In fc
   If f1.datecreated > temp Then temp = f1.datecreated: myname = f1.Path
Next
Set f = fs.GetFolder(myname): Set fc = f.Files
For Each f1 In fc
    If f1.Size > temp Then temp = f1.Size: myname = f1.Name
Next: Debug.Print temp & vbCrLf; myname: End Sub

* the search for folder is done on the basis of creation date

**snb** · 11-30-2010, 11:45 AM

The largest file in the most recent folder:

Sub largest_file_in_most_recent_folder()
  c00 = "E:\OF\"
  c01 = "E:\text.txt"
    
  For j = 1 To 2
    If Dir(c01) <> "" Then Kill c01
    Open c01 For Output as #1
    Close #

    Shell "cmd Dir " & c00 & Choose(j, "*.", "*.??*") & Choose(j, " /b /o-d > ", " /b /o-s > ")  & c01

    Do
      DoEvents
    Loop Until FileLen(c01) > 0
        
    Open c01 For Input As #1
      c00 = c00 & Split(input(LOF(1), #1), vbCrLf)(0) & IIf(j = 1, "\", "")
    Close #1
  Next
End Sub

**WAW** · 11-30-2010, 12:53 PM

Thanks for the code guys, appreciate it.

Unfortunately, I am unable to get either to work.

snb - opens CMD but hangs "File Not Found" on the first Loop.

**snb** · 11-30-2010, 01:00 PM

What did yo do to improve the code ?
Meanwhile I added something to my previous post.

**watersev** · 11-30-2010, 01:36 PM

Originally Posted by WAW

Thanks for the code guys, appreciate it.

Unfortunately, I am unable to get either to work.

snb - opens CMD but hangs "File Not Found" on the first Loop.

Please try this way:

Sub Folder_and_file()
Dim fs, f, f1, fc, s, folderspec, temp, myname As String
Set fs = CreateObject("Scripting.FileSystemObject")
folderspec = "C:\aftere\Adobe CS4\deployment"
On Error GoTo Handler: Set f = fs.GetFolder(folderspec)
On Error GoTo Handler1: Set fc = f.subfolders
For Each f1 In fc
   If f1.datecreated > temp Then temp = f1.datecreated: myname = f1.Path
Next
Set f = fs.GetFolder(myname): Set fc = f.Files
For Each f1 In fc
    If f1.Size > temp Then temp = f1.Size: myname = f1.Name
Next: Debug.Print temp & vbCrLf; myname: Set f = Nothing: Set fc = Nothing
Exit Sub
Handler:
MsgBox "Folder does not exist", vbCritical
Handler1:
MsgBox "This folder does not have subfolders inside", vbCritical
End Sub