Move files based on an Excel list

I have a repetitive task each month to move files from one location to another based on the file name.  In this case, we have a PDF file that has a name of a person.  Example: Doe, John (1234).pdf
I would like to create an Excel list that has two columns.  The first column would have the name of the destination folder and the second column would have the name of the file.  The name of the file never changes, only the content of the file for that user.  There are about 200 files that will be moved during each session.
Column A = Folder name- JohnD
Column B = File name- Doe, John (1234).pdf

I would like to find a script that will loop through each file in the origin folder
Get the file name
Lookup the file name in the Excel list
If the file name is found, get the destination path/folder from the matching column
Move the found file to the destination folder
Overwrite the file in the destination if it exists
Delete the original file

Any suggestions?
ITMcmcpaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Justin EllenbeckerIT DirectorCommented:
You can do this also with a pipe delimited file which would keep your files sizes down excel might be a little bulky.  This script will read in the folder|filename and then I did it a little backwards but still gets the same results it searches for the file name and if it matches what it read from the txt it moves it.


Set oFS = CreateObject("Scripting.FileSystemObject")
Set readfile = oFS.OpenTextFile("C:\path\source.txt",1,False)
Set oFolder = oFS.GetFolder("C:\folderwithfiles\")
Do While Not readfile.AtEndOfStream
	linedata = Split(readfile.ReadLine,"|")
	For Each file In oFolder.Files
		If Lcase(file.name) = LCase(linedata(1)) Then
			oFS.CopyFile file.path, "C:\" & linedata(0) & "\" & file.name, True
		End If
	Next
Loop

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ITMcmcpaAuthor Commented:
Thank you very much for the quick response.  This worked very well.  I made a slight adjustment to delete the source file and it does exactly what I needed.

oFS.DeleteFile ("C:\sourcepath\" & file.name)

Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.