Excel help - Find and copy files

[Ikenest]

I need to find file names - from a list in an excel file - that match file
names (with an extension, i.e., 1466319001_20080923193500.tif) located in one
directory folder and save the matched files to a different directory folder.

Thanks,

 

[Joel]

The code will look something like the code below. I don't know if the
filenames on the spreadsheet contain the directory or the tif extension.
Modify the code as necessary. Change OldFolder and NewFolder as required.


Sub copyfiles()

OldFolder = "c:\temp\"
NewFolder = "c:\temp\test\"

With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
FName = .Range("A" & RowCount)
FileCopy Source:=OldFolder & FName & ".tif", _
Destination:=NewFolder & FName & ".tif"
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Ikenest]

Hi Joel,

Thanks for looking into this. I tried and used your code, but I get an error
message (Run-time error '53': File not found). The filenames on the excel
document only contain tif extension. So I am trying to copy and move files
which filenames match ONLY the filenames in my excel document from the files'
directory to a new directory. See below

Sub copyfiles()

OldFolder = "G:\LMG\RECOVERY\AGENCY\Portfolio Sale\2008\A-Program2 Sale
Files\Imaged Copies\Disk1\Disk 1\"
NewFolder = "G:\LMG\RECOVERY\AGENCY\Portfolio Sale\2008\A-Program2 Sale
Files\Imaged Copies\BackUp\"

With Sheets("All")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
FName = .Range("A" & RowCount)
FileCopy Source:=OldFolder & FName & ".tif", _
Destination:=NewFolder & FName & ".tif"
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Joel]

I added a test to make sure the file existed.


OldFolder = "G:\LMG\RECOVERY\AGENCY\Portfolio Sale\2008\A-Program2 Sale
Files\Imaged Copies\Disk1\Disk 1\"
NewFolder = "G:\LMG\RECOVERY\AGENCY\Portfolio Sale\2008\A-Program2 Sale
Files\Imaged Copies\BackUp\"

With Sheets("All")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
FName = .Range("A" & RowCount)
CheckName = Dir(OldFolder & FName & ".tif")
if CheckName <> "" then
FileCopy Source:=OldFolder & FName & ".tif", _
Destination:=NewFolder & FName & ".tif"
Else
MsgBox("Could Not find file : " & FName)
End iF
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Ikenest]

Thanks Joel!

Thanks for your prompt response. I keep getting prompted with the Message
("Could Not find file : " & FName) and no file is copied to the "Backup
Folder".

Sincerely,

 

[Joel]

1) Does the spreadsheet have the TIF on the end of the file?
If it does make the modifications below. Also check carefully that the file
names in the spreadsheet matches the files in the directory.

2) Does the filenames on the spreadsheet contain the folder and the
filename? If so I need to make additional changes.

Note: The Folder names must be corrrect otherwise excel would give the Error
PATH not found.


Sub copyfiles()

OldFolder = "c:\temp\"
NewFolder = "c:\temp\test\"

With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
FName = .Range("A" & RowCount)
FileCopy Source:=OldFolder & FName, _
Destination:=NewFolder & FName
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Ikenest]

1) Below is a sample filename in Sheet 'All', cell (A1) - on the spreadsheet:

1458600001_20080924094025.tif

2) No, the filenames on the spreadsheet only have the filename

Below is the sample of how files are lined up in the folder
(G:\LMG\RECOVERY\AGENCY\Portfolio Sale\2008\A-Program2 Sale Files\Imaged
Copies\Disk1\Disk 1)

In the folder:
Name
1458600001_20080924094025.tif

Size
1,324

Type
TIF File

Date Modified
9/24/2008 9:24 AM

Thanks a bunch for your assistance

 

[Ikenest]

THANKS A TON !!!!

It has worked perfectly, just like I wanted it to. YOU rock!!!!!!!!!!

 

[Ikenest]

Joel,

One more question, it looks like all the files are being copied to the
NewFolder, regardless of being a match or not:

What I need to accomplish is to be able:

1) Find filenames from the open excel spreadsheet that match the files in
the OldFolder
2) Copy ONLY the matched file, NOT all files

I guess we are in the right direction.

Thanks again,

 

[Joel]

The code gets the source filename from the spreadsheet so it will only copy
the files that exist in the spreadsheet. Try copying one of the files from
the source directory back into the source directory so you get Copy of
abc.tif. Then run the program again and see if you get the copy into the
destination directory.

 

[Ikenest]

Hi Joel,

Sorry that I could not get to you sooner ... YOU ROCK!!! The files have
been copied as expected. Thanks for your patience and assistance in making
things easier for me