Moving oldest files first from source to destination

0 votes

I moved the more than 2000 files I had in a source using the code below. The entire process was functioning well, but today (two weeks later), when I begin moving the files once more, it begins to provide an error. "Run Time Error 58" and "File Already Exist" are the errors.

Following inspection, it was discovered that there was no such file in the source or destination folders, and all of the file names were distinct. Even when I clear the Source folder (just to check), the problem still occurs. Can somebody tell me whether I'm doing something wrong or why this issue is occurring even if both files are empty?

Function OldestFile(strFold As String) As String
Dim FSO As Object, Folder As Object, File As Object, oldF As String
 Dim lastFile As Date: lastFile = Now
   Set FSO = CreateObject("Scripting.FileSystemObject")
   Set Folder = FSO.GetFolder(strFold)
   For Each File In Folder.Files
        If File.DateCreated < lastFile Then
            lastFile = File.DateCreated: oldF = File.Name
        End If
   Next
   OldestFile = oldF
End Function


Sub MoveOldestFile()
    Dim FromPath As String, ToPath As String, fileName As String, limit As Long
    FromPath = "C:\Users\user\Desktop\Source\"
    ToPath = "C:\Users\user\Desktop\Destination\"
    limit = 20
    filesmoved = 0
    fileName = OldestFile(FromPath)
   Do Until fileName = "" Or filesmoved = limit
      If Dir(ToPath & fileName) = "" Then
            Name FromPath & fileName As ToPath & fileName
          
            filesmoved = filesmoved + 1
        End If
        fileName = OldestFile(FromPath)
    Loop
End Sub
Feb 24, 2023 in Others by Kithuzzz
• 38,010 points
401 views

1 answer to this question.

0 votes

You have to change the following line:

If Dir(ToPath & fileName) = "" Then

To

If Dir(ToPath & fileName, 7) = "" Then

The check for read-only, system, or hidden files is not being done by that line. Hence, your code tries to rename a hidden file that already exists.

You might want to try this version of your code, which, because it doesn't traverse through the entire directory each time, should perform better in large directories.

Function OldestFile(strFold As String) As Variant
    Dim FSO As Object, Folder As Object, File As Object, oldF As String
    Dim lastFile As Date: lastFile = Now
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Folder = FSO.GetFolder(strFold)
    Dim myarray As Variant
    If Folder.Files.Count > 0 Then
        ReDim myarray(Folder.Files.Count - 1)
        ix = 0
        For Each File In Folder.Files
            myarray(ix) = Format(File.DateCreated, "YYYYMMDDHHmmSS") & File.Name
            ix = ix + 1
        Next
        For i = LBound(myarray) To UBound(myarray) 'Sort according to date
            For j = i + 1 To UBound(myarray)
                If UCase(myarray(i)) > UCase(myarray(j)) Then
                    Temp = myarray(j)
                    myarray(j) = myarray(i)
                    myarray(i) = Temp
                End If
            Next j
        Next i
    End If
OldestFile = myarray
End Function


Sub MoveOldestFile()
    Dim FromPath As String, ToPath As String, fileName As String, limit As Long
    Dim fileArray As Variant
    FromPath = "C:\Users\user\Desktop\Source\"
    ToPath = "C:\Users\user\Desktop\Destination\"
    limit = 20
    filesmoved = 0
    fileArray = OldestFile(FromPath)
    If Not IsEmpty(fileArray) Then
        ix = 0
        Do Until ix > UBound(fileArray) Or filesmoved = limit
            fileName = Mid(fileArray(ix), 15)
            If Dir(ToPath & fileName, 7) = "" Then
                Name FromPath & fileName As ToPath & fileName
                filesmoved = filesmoved + 1
            End If
            ix = ix + 1
        Loop
    End If
End Sub
answered Feb 24, 2023 by narikkadan
• 63,420 points

Related Questions In Others

+1 vote
0 answers

where to put tf files while building servers from terraform.. its creating only workspace.

# kitchen create -----> Starting Kitchen (v2.2.5) -----> Creating ...READ MORE

Aug 4, 2019 in Others by Priyanka
432 views
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
13,130 views
0 votes
1 answer

How to open .xlsx files in MS Excel from VS Code?

Hello, to open xlxs files, or files ...READ MORE

answered Feb 17, 2022 in Others by gaurav
• 23,260 points
2,894 views
0 votes
0 answers

Copying values from multiple excel files to a single one with python

I want to replicate the values from ...READ MORE

Apr 11, 2023 in Others by Kithuzzz
• 38,010 points
615 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
836 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,138 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
433 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
698 views
0 votes
1 answer

How to get columns from Excel files using Apache POI?

The only way to see all the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,420 points
4,008 views
0 votes
1 answer

Better SEO to remove "stop" words from an article's URL Slug?

these words doesnt have much priority in ...READ MORE

answered Feb 20, 2022 in Others by narikkadan
• 63,420 points
344 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP