MOVE FILE IN VBA

Question

I want the code below to use 2 methods. the first method is copy file and the second method is move file. For method 1 I did a comment so that to do method 2 but it didn't work there was an error.

Sub movecopyFiles()
  Dim sh As Worksheet, lastR As Long, arrA, i As Long, k As Long
  Dim fileD As FileDialog, strDestFold As String, FSO As Object
  
  Set sh = ActiveSheet
  lastR = sh.Range("A" & sh.Rows.Count).End(xlUp).Row ' last row on A:A column
  arrA = sh.Range("A2:E" & lastR).Value2                   'place the range in an array for faster iteration
  Set FSO = CreateObject("Scripting.FileSystemObject")
  With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select the destination folder!"
        .AllowMultiSelect = False
        If .Show = -1 Then
            strDestFold = .SelectedItems.Item(1) & "\"   'select the destination folder
        End If
  End With
  If strDestFold = "" Then Exit Sub                         'in case of  not selecting any folder
  For i = 1 To UBound(arrA)
     If UCase(arrA(i, 5)) = "V" Then                         'copy the file only if a "V" exists in column E:E
        If FSO.FileExists(arrA(i, 1)) Then                    'check if the path in excel is correct
'            FSO.COPYFILE arrA(i, 1), strDestFold, True     'copy the file (True, to overwrite the file if it exists)
            FSO.moveFILE arrA(i, 1), strDestFold, True     'move the file (True, to overwrite the file if it exists) >> error this line 
            k = k + 1
        Else
            MsgBox arrA(i, 1) & " file could not be found." & vbCrLf & _
                        "Please, check the spelling and correct the file full path!", vbInformation, _
                        "File does not exist..."
        End If
     End If
  Next i
  MsgBox "Copied " & k & " files in " & strDestFold, , "Ready..."
End Sub

From what I remember, this method has only two arguments. So, it should become `FSO.moveFILE arrA(i, 1).value, strDestFold`. — FaneDuru, Jan 15 '22 at 15:41
If you better like `MoveFile` but you want overwriting, you cannot `build` `force` a parameter which does not exist in the method. You can use `FSO.COPYFILE` followed by `FSO.DeleteFile arrA(i, 1).value`. There are some other methods, too. Or check if file exists and `Kill` it before moving... — FaneDuru, Jan 15 '22 at 15:52
@FaneDuru , `FSO.moveFILE arrA(i, 1).value, strDestFold` i use this but the answer for value i.e. variable not defined. I only want to use the first 2 methods of copyfile, if I want to use move file, I just need to comment the code for the copyfile code. I think you can give me in the form of your answer. If possible, the code structure that you create is never changed. I need to do a comment or uncomment or if you have the best recommendation — roy, Jan 15 '22 at 16:15
Ups... Oh, no, I was wrong, I took the array as a range. Please, remove `value`. In the second case, too... :) — FaneDuru, Jan 15 '22 at 16:27
@FaneDuru , `FSO.moveFILE arrA(i, 1), strDestFold` If I use the code it works but if there is in the destination folder there is the same file then there is an error "file already exists". What is the solution if I keep forcing the file move? — roy, Jan 15 '22 at 16:35
Please, read carefully my above comments. Of course, forget about the array `Value` issue... — FaneDuru, Jan 15 '22 at 16:42
@FaneDuru , is there any other solution so as not to forget the array ? — roy, Jan 15 '22 at 16:54
I did not suggest to forget the 'array'... The `Value` I put there by mistake. It is not so clear what I suggested, I will try posting an answer. Not exactly right now, since I have something else to finish... — FaneDuru, Jan 15 '22 at 17:04
OK. Please, check the way I suggested in my answer and send some feedback. — FaneDuru, Jan 15 '22 at 17:17

score 0 · Accepted Answer · answered Jan 15 '22 at 17:16

Please, adapt the following part in this way:

 Dim fileName As String  'new variable to be declared
 'your existing code...
      If FSO.FileExists(arrA(i, 1)) Then                       'check if the path in excel is correct
                fileName = Right(arrA(i, 1), Len(arrA(i, 1)) - InStrRev(arrA(i, 1), "\"))
                If FSO.FileExists(strDestFold & fileName) Then Kill strDestFold & fileName 'delete file if exists
                FSO.MoveFile arrA(i, 1), strDestFold           'move the file 
                k = k + 1
        Else
                MsgBox arrA(i, 1) & " file could not be found." & vbCrLf & _
                            "Please, check the spelling and correct the file full path!", vbInformation, _
                            "File does not exist..."
        End If
  'your existing code

@roy Didn't you find some time to check the above suggestion? If tested, didn't it return what you need? — FaneDuru, Jan 16 '22 at 11:07

MOVE FILE IN VBA

1 Answers1