Find & Copy Files Listed in Excel Worksheet ?

[bogdog]

I have a list of filenames (complete with file extensions) in a single column
in an Excel Worksheet. All of these listed files are in the same folder
(along with many other files) on the hard drive. Each filename in the list
is hyperlinked to the actual filespec (drive, folder, filename). The list
may contain several hundred filenames. How do I read the list of filenames,
find the actual files, and copy them all at once into a separate folder on
the hard drive? I can manually search with Win Explorer and select each file
using Ctrl-Click & mass copy using Paste, but its very laborious. Is there a
commercial software product to accomplish this job? I'm using Win Vista Home
Premium & Excel 2007. Also have XP Pro & Excel 2003 available.

 

[cathellisuk]

you could have your VBA macro read the rows in the Excel sheet one at
a time.
For each file name use the InStrRev function to find the base name of
the file (ie to strip of the drive and folder part of the file name)
then use the FileCopy statement to copy the file from its original
location to the target folder .

Catherine

 

[bogdog]

you could have your VBA macro read the rows in the Excel sheet one at
a time.
For each file name use the InStrRev function to find the base name of
the file (ie to strip of the drive and folder part of the file name)
then use the FileCopy statement to copy the file from its original
location to the target folder .

Catherine

Hi Catherine ...

Thanks for your suggestion ... will give it a go. I can easily delete the
hyperlinks & end up with a list of base filenames (with Extensions, but
stripped of Drive and Folder) in a single column in the worksheet. I'm an
amateur with VBA, but can Do Loop thru the list & use the FileCopy Statement
on each row (cell) in the list. Not sure how to program substitution of the
filename (in each cell of the worksheet) into SourceFile and DestinationFile
in the FileCopy Statement. Will pursue it ... thanks again for your help!