Macro to Copy and Rename Files in Different Directory.

[calebga]

Hi,

In column A is filename. I need a macro to open the file, rename it to the text listed in column B, then save it to a new location. Any takers?

Thanks in advance!

 

[rpaulson]

this should move them and rename

Sub Rename_files()
oPath = "C:\test\" 'Orginal Path
nPath = "C:\results\" ' new path

For r = 2 To 4 'adjest range as needed
Name oPath & Cells(r, "A") As nPath & Cells(r, "B")
Next r
End Sub

hth,

Ross

 

[calebga]

Thanks, Ross.

This is almost it, but it needs to copy the original file first, then move and rename. A single original file may need to be renamed several times, so the file must stay in its original location with the original name. Is that doable?

Again, I really appreciate the help!

-Caleb

 

[rpaulson]

UNTESTED

Sub Rename_files()
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")

oPath = "C:\test\" 'Orginal Path
nPath = "C:\results\" ' new path

For r = 2 To 4 'adjest range as needed
fso.CopyFile(oPath & Cells(r, "A"),nPath & Cells(r, "B")
Next r
End Sub

Hth,

Ross

 

[calebga]

Ross,

It's giving a syntax error for the following line

fso.CopyFile(oPath & Cells(r, "A"),nPath & Cells(r, "B")

 

[rpaulson]

fso.CopyFile(oPath & Cells(r, "A"),nPath & Cells(r, "B")
might need to add another ) to the end
fso.CopyFile(oPath & Cells(r, "A"),nPath & Cells(r, "B") )

 

[calebga]

Sorry, I meant to add that I'd done that already. It's still saying syntax error. Does fso need to be defined?

 

[rpaulson]

lets modify the first one

Sub Rename_files()
oPath = "C:\test\" 'Orginal Path
nPath = "C:\results\" ' new path

For r = 2 To 4 'adjest range as needed
FileCopy oPath & Cells(r, "A"), nPath & Cells(r, "B")
Next r
End Sub

Ross

 

[calebga]

I believe that's done it. Thanks!