File not moving VBA

Question

Call MOVEDFFILES("C:\TEMP\MAIN FOLDER\INVOICES\COUNTRY\Invoices\" & SEASON & " DF Invoices\", "C:\TEMP\MAIN FOLDER\INVOICES\COUNTRY\Invoices\"  & SEASON & " DF Invoices\Imported\")

I have created the above code to call the below code and move files if they don't exist in the new folder, and delete them in the original folder if they do. however whilst I can use Name OldLocation & MyFile As NewLocation & MyFile to move the files, they dissapear when trying to use the code below. This code works else where for a different file path, the only difference is using *.csv as MyFile, could this cause an issue?

Private Sub MOVEDFFILES(OldLocation As Variant, NewLocation As Variant)

'Makes the file path if not there
    If Dir(NewLocation, vbDirectory) = "" Then
        MkDir NewLocation
    End If

'Moves the files from one location to another
MyFile = Dir(OldLocation & "*.csv")

Do Until MyFile = ""

If Not NewLocation & MyFile > 0 Then

    Name OldLocation & MyFile As NewLocation & MyFile

Else

    Kill OldLocation & MyFile

End If


MyFile = Dir

Loop

End Sub

the check `If Not NewLocation & MyFile > 0 Then` cannot work. You are comparing a (concatenated) string with a number! — FunThomas, Nov 15 '18 at 17:19

score 1 · Accepted Answer · answered Nov 15 '18 at 17:39

The problem is that your check if the file exists in the new location is wrong.

Easiest way to check it would be to issue a Dir-command, but that would break your loop. You can have only one Dir command open, issuing a Dir within the loop to check if the file exists in the new location would cause the command MyFile = Dir fail to check for the next file in the old location.

Turns out that you don't have to do the check at all: Simply issue both, the Name and the Kill command. Trick is to ignore any errors. If the file doesn't exists in the new location, the Name would move it and the Kill doesn't have to delete anything because the file is already gone.. If the file already exists in the new location, the Name will fail and the Kill will do it's job...

So, this is one of the really few situations to use the infamous On Error Resume Next:

f = Dir(OldLocation & "*.csv")
Do Until f = ""
    On Error Resume Next
    Name OldLocation & f As NewLocation & f
    Kill OldLocation & f
    On Error GoTo 0
    f = Dir
Loop

Thomas you have made my morning - it works perfectly! I'm still fairly new to coding so I try and take the simple options, you have written me some beautiful code there :) I did try the on error resume next process myself but I didn't quite grasp how it worked and crashed the whole database.... the on error goto 0 part is clearly important! — Carly Dewdney, Nov 16 '18 at 10:17
Generally, you should never use the `On Error Resume Next` - except for the (rare) specific cases where you know that a statement may issue an error, and you will take care about that by yourself (see for example https://stackoverflow.com/a/6688482/7599798). But you always should issue the `On Error Goto 0` **immediately** after that statement. — FunThomas, Nov 16 '18 at 10:42

File not moving VBA

1 Answers1