Move files listed in Excel to a certain folder

[MMShaggy]

I have multiple excel files that have everything on Sheet2 and row "A" with file names such as:

2012-015GHEW-BW-1981-b1_1.0_0001.tif
2012-015GHEW-BW-1982-b1_3.0_0014.tif
2012-015GHEW-BW-1982-b1_7.1_0008.tif

I would like to use these Excel files to move all files listed from folder "C:\Test" to a folder named "C:\Test2", I thought I could do it with one of your VB script's but I get one error

"Run-time error '76'"
Path not found.

the error is on this line of code

FileCopy oldFile, newFile

This is the full script I'm using:

Sub SetUp4a()  Dim rng As Range
  Dim oldPath As String
  Dim newPath As String
  Dim oldFile As String
  Dim newFile As String
  
  '================================================
  'Set the paths to the folders you are processing
  'REMEMBER END BACKSLASH
  '================================================
  oldPath = "C:\Test\"
  newPath = "C:\Test2\"
  
  '=======================================================
  'set up the start of the range you want to loop through
  'EDIT SHEET NAME AND FIRST CELL ADDRESS IF NECESSARY
  '======================================================
  Set rng = Sheets("Sheet2").Range("A1")
  
   '==================================================
  'loop through column B until you find an empty cell
  '==================================================
  Do Until rng = ""
      
      'build up the full path to the old file
      oldFile = oldPath
      
      'build up the full path to the new file you want to create
      newFile = newPath
  
      'copy the old file to the new new folder
      FileCopy oldFile, newFile
    
    'get the next file by moving down one row
    Set rng = rng.Offset(1, 0)
  Loop
End Sub

 

[Domenic]

You'll need to replace...

oldFile = oldPath

with

oldFile = oldPath & rng.Value

And, you'll need to replace...

newFile = newPath

with

newFile = newPath & rng.Value

Also, since you want to move those files from one folder to another, you'll need to use the Name statement. So you'll need to replace...

FileCopy oldFile, newFile

with

Name oldFile As newFile

Hope this helps!

 

[MMShaggy]

You'll need to replace...

oldFile = oldPath

with

oldFile = oldPath & rng.Value

And, you'll need to replace...

newFile = newPath

with

newFile = newPath & rng.Value

Also, since you want to move those files from one folder to another, you'll need to use the Name statement. So you'll need to replace...

FileCopy oldFile, newFile

with

Name oldFile As newFile

Hope this helps!

Thank you for the quick reply, I made the changes but the same line is still giving me a Run-time error '53' "File not found".
Would this happen if all files in the list are not in the source folder? If so is there a way to continue looking down the list and moving only the ones it does find in the source folder? Thank you again for your expertise I really appreciate your help.

Sub SetUp4a()  Dim rng As Range
  Dim oldPath As String
  Dim newPath As String
  Dim oldFile As String
  Dim newFile As String
  
  '================================================
  'Set the paths to the folders you are processing
  'REMEMBER END BACKSLASH
  '================================================
  oldPath = "C:\Test\"
  newPath = "C:\Test2\"
  
  '=======================================================
  'set up the start of the range you want to loop through
  'EDIT SHEET NAME AND FIRST CELL ADDRESS IF NECESSARY
  '======================================================
  Set rng = Sheets("Sheet2").Range("A1")
  
   '==================================================
  'loop through column B until you find an empty cell
  '==================================================
  Do Until rng = ""
      
      'build up the full path to the old file
      oldFile = oldPath & rng.Value
      
      'build up the full path to the new file you want to create
      newFile = newPath & rng.Value
  
      'copy the old file to the new folder
      Name oldFile As newFile
    
    'get the next file by moving down one row
    Set rng = rng.Offset(1, 0)
  Loop
End Sub

 

[Domenic]

You can use the Dir and Len functions to test whether the file exists. The Dir function returns the filename if it exists, otherwise it returns an empty string. Then you can use the Len function to test for a filename or empty string.

[FONT=Courier New]    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      
      [COLOR=green]'build up the full path to the old file[/COLOR]
      oldFile = oldPath & rng.Value
      
      [COLOR=darkblue]If[/COLOR] Len(Dir(oldFile, vbNormal)) > 0 [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'build up the full path to the new file you want to create[/COLOR]
        newFile = newPath & rng.Value
    
        [COLOR=green]'copy the old file to the new folder[/COLOR]
        Name oldFile [COLOR=darkblue]As[/COLOR] newFile
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'get the next file by moving down one row[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
  [COLOR=darkblue]Loop[/COLOR][/FONT]

Hope this helps!

 

[MMShaggy]

Perfect, thank you so much, I can not express how much this helped me out.