+ Reply to Thread
Results 1 to 8 of 8

VBA Find Latest Folder then largest file.

Hybrid View

  1. #1
    Registered User
    Join Date
    01-05-2010
    Location
    liverpool, england
    MS-Off Ver
    Excel 2007
    Posts
    44

    VBA Find Latest Folder then largest file.

    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.

  2. #2
    Registered User
    Join Date
    01-05-2010
    Location
    liverpool, england
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: VBA Find Latest Folder then largest file.

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

  3. #3
    Registered User
    Join Date
    01-05-2010
    Location
    liverpool, england
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: VBA Find Latest Folder then largest file.

    Any Ideas on this one anyone?

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA Find Latest Folder then largest file.

    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
    Last edited by watersev; 11-30-2010 at 11:05 AM.

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA Find Latest Folder then largest file.

    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
    Last edited by snb; 11-30-2010 at 12:58 PM.



  6. #6
    Registered User
    Join Date
    01-05-2010
    Location
    liverpool, england
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: VBA Find Latest Folder then largest file.

    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.

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA Find Latest Folder then largest file.

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

  8. #8
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA Find Latest Folder then largest file.

    Quote Originally Posted by WAW View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1