Moving files to another directory

Question

I am trying to move several thousand documents from a list in one column and then move them to the folders listed in the other column, and then finally a third column with what has been moved and what hasn't (there will be errors where the file doesn't exist.

I know how to do it on a file by file basis as below:

How do I do it for the whole columns though?

Sub Copy_One_File()
  FileCopy "C:\Users\Ron\SourceFolder\Test.xls", "C:\Users\Ron\DestFolder\Test.xls"
End Sub

Sub Move_Rename_One_File()
  'You can change the path and file name
  Name "C:\Users\Ron\SourceFolder\Test.xls" As "C:\Users\Ron\DestFolder\TestNew.xls"
End Sub

You would need a loop. (and those are not called hyperlinks, those are just filenames with full paths) — braX, Feb 24 '20 at 12:00
https://stackoverflow.com/questions/24377197/iterating-through-populated-rows-in-excel-using-vba — braX, Feb 24 '20 at 12:04
Maybe look into `FSO` File system objects, you can loop through folder contents. — Daghan, Feb 24 '20 at 12:45

score 1 · Accepted Answer · answered Feb 24 '20 at 12:52

1

If these 3 columns are columns "A", "B" and "C", this code should probably work.

Sub move_files()
  Dim i As Long
  With ActiveSheet
    For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
      Err.Clear
      On Error Resume Next
      Name (.Cells(i, 1)) As .Cells(i, 2) & "\" & StrReverse(Split(StrReverse(.Cells(i, 1)), "\")(0))
      If Err = 0 Then .Cells(i, 3) = "YES" Else .Cells(i, 3) = "NO"
      On Error GoTo 0
    Next
  End With
End Sub

answered Feb 24 '20 at 12:52

ZygD

22,092
39
79
102

Hi, thanks this works. Much appreciated. One follow up question, if I wanted to copy the file rather then move it or delete, is it an easy substitute? Thanks, Scott. – Scottyp Feb 24 '20 at 22:43
Hey, happy to help. I have not tested the copying part, but it seems you should be able to do it by replacing something in the middle line: `Name` with `FileCopy` and `As` with `,` – ZygD Feb 25 '20 at 14:32

score 0 · Answer 2 · answered Feb 24 '20 at 13:00

0

Try this code, please...

Sub testCopyFiles()
 Dim sh As Worksheet, lastRow As Long, i As Long, destPath As String
 Dim fN As String, fileName As String
 Set sh = ActiveSheet
 lastRow = sh.Range("A" & Cells.Rows.count).End(xlUp).row

 For i = 2 To lastRow
    fN = sh.Range("A" & i).Value
    destPath = sh.Range("B" & i).Value & "\" & _
                Right(fN, Len(fN) - InStrRev(fN, "\"))
    FileCopy sh.Range("A" & i).Value, destPath
    sh.Range("C" & i).Value = "Yes"
 Next i
End Sub

answered Feb 24 '20 at 13:00

FaneDuru

38,298
4
19
27

Thanks for the post, it is coming up as an error on: FileCopy sh.Range("A" & i).Value, destPath – Scottyp Feb 24 '20 at 22:05
@Scottyp: I missed the part saying that some paths can be wrong. The code could be easily adapted to preliminary check if file or folder exist and return (in parenthesis) the wrong one... Or, only skip the error and let you visually check which of the two paths are wrong. – FaneDuru Feb 25 '20 at 08:20

Moving files to another directory

2 Answers2

Linked