Excel Hints

Results 1 to 4 of 4

Thread: Solved: fso.GetFile not working!

  1. #1

    Solved: fso.GetFile not working!

    I have a sub routine that counts the files in a folder and if the # is greater than 15 it deletes the earliest files (by datecreated) until the # is <= 15. The code worked great for about a month, and not I get a runtime erro '53', "File Not Found". The problem is that the file is there, I checked! Any ideas, suggestions, etc. The error occurs on this line "Set f = fso.GetFile(FileName)"

    [vba]Sub Folder_Maintenance()
    Dim fso As Object, ObjFiles As Object, _
    Folder As Object, f As Object, indFile, _
    Dir As String
    Dim FileName As String, MinDC As Double
    Dir = "R:\Capital Projects\Status Reports\Project Resource Management Form\Log Files"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Folder = fso.getfolder(Dir)
    Set ObjFiles = Folder.Files
    While ObjFiles.Count > 15

    For Each indFile In ObjFiles
    MinDC = indFile.datecreated
    FileName = indFile.Name
    Exit For
    Next

    For Each indFile In ObjFiles
    If indFile.datecreated < MinDC Then
    MinDC = indFile.datecreated
    FileName = indFile.Name
    'MsgBox (MinDC)
    'MsgBox (FileName)
    Else
    End If
    Next
    Dim a
    Set f = fso.GetFile(FileName)
    f.Delete

    Wend
    End Sub
    [/vba]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,092
    Location
    [vba]

    Sub Folder_Maintenance()
    Dim fso As Object, ObjFiles As Object, _
    Folder As Object, f As Object, indFile, _
    Dir As String
    Dim FileName As String, MinDC As Double
    Dir = "R:\Capital Projects\Status Reports\Project Resource Management Form\Log Files"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Folder = fso.getfolder(Dir)
    Set ObjFiles = Folder.Files
    While ObjFiles.Count > 15

    For Each indFile In ObjFiles
    MinDC = indFile.datecreated
    FileName = indFile.Path
    Exit For
    Next

    For Each indFile In ObjFiles
    If indFile.datecreated < MinDC Then
    MinDC = indFile.datecreated
    FileName = indFile.Path
    'MsgBox (MinDC)
    'MsgBox (FileName)
    Else
    End If
    Next
    Dim a
    Set f = fso.GetFile(FileName)
    f.Delete

    Wend
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Thanks so much....I didn't even think of .Path vs .Name. Works great!

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,092
    Location
    It probably worked before because you were already in that directory.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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