vbafsomovefile

MOVE FILE IN VBA


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

error


Solution

  • 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