View Full Version : How To Copy Paste File Named in Cell to Another Folder Named in Cell?

07-14-2011, 06:40 PM
Step1 or Step2:-
Source file/folder: I have file names specified in one column A2:A10 and their folder addresses in the corresponding rows in the next column B2:B10.

Step2 or Step3:-
Destination file/folder: I want to copy all these files to another folders (folder addresses specified in column C2:C10).

Step3 or Step1:-
Rename files: Then rename these file names (names that shown in column A2:A10) to other file names specified in their corresponding rows in column D2: D10.

Can either start by copying/pasting then renaming OR renaming the file names before copying and pasting. Can anyone assist me please? www--> vbaexpress.com/kb/getarticle.php?kb_id=827 this page should help me a little bit. I tried his codes before modifying to what I need but it doesn't work after I changed the source folder and destination folder, and removed all the green comments and run it. A compile error message pops up saying "User-defined type not defined" --> pointing at the codes "objFSO As FileSystemObject" :dunno

Kenneth Hobs
07-16-2011, 11:18 AM
It may not be clear from the kb but it was sort of explained in the first comment. A more detailed explanation is that from VBE, select menu items Tools > References, and then select, Microsoft Scripting Runtime. Most computers have that DLL file installed.

Of course you don't need the scripting object to do what you need. Doing projects like yours is fairly simple. They can be more robust as in the kb entry when all cases of possible problems like no drive, folder, or files exists.

If you need more help or want to see a method without the scripting object method, post back.

07-27-2011, 01:12 AM
Hi Kenneth Hobs

I've got myself a simple way of coding which I prefer as it's easy to understand:-

Sub Copy_Files()
Range(Selection, Selection.End(xlDown)).Select
n = Selection.End(xlDown.Row
For i = 2 To n
FileCopy Source:=Cells(i, 1).Value & Cells(i, 2).Value, _
Destinatino:=Cells(i, 3).Value & Cells(i, 4).Value
Next i
MsgBox "File copy complete."
End Sub

But...when I want to empty (delete all files inside) the folders named in column C before I copy files from column B in forlders named column A. I tried using 'Kill' --> FileKill Source:=, Kill Source:=, Kill:=, Kill Path:=, etc...

Kill Path:=Cells(i, 3).Value

...none of these seem like the correct name arguments.

Anyone could help? :confused:

Kenneth Hobs
07-27-2011, 07:53 AM
Your code is more simple because you did not check for as many issues as in the kb entry. Your code has some syntax errors so I am not sure how it works but the concept will work as-long-as an issue that causes an error does not crop up.

The Kill command is used to kill or delete a file or files. Before deleting a file, one should verify that the file exists. Dir() can be used check for file or folder existence. To kill all files in folder c:\t\t:

Kill "c:\t\t\*"

Kenneth Hobs
07-27-2011, 05:48 PM
Crossposted (http://www.mrexcel.com/forum/showthread.php?p=2805858)