Copy files

  • Thread starter Thread starter saybut
  • Start date Start date
S

saybut

Hi,

I need to know how to copy files from one folder to a different folde
selecting only the files listed in a range of cells.

The range with the file names in would by column a, the files wil
always be in the same folder where they are being taken from, and wil
always be copied into the same destination folder.

any help would be appreciated.

Regards,

Mark Pitchford
 
Hi
try something like the following

Sub copy_files()
Dim rng as range
dim cell as range
Dim SrcFilename As String
Dim DestFilename As String

Set rng = selection
For each cell in rng
SrcFilename = "C:\Temp\" & cell.Value 'change source path
DestFilename = "C:\Test\" & cell.Value 'change destination
path
FileCopy SrcFilename, DestFilename
next
end sub

Note: Not much error checking included (e.g. file already exist in the
destination path, etc.)
 
Hi Frank,

thanks very much for the code, it works great, there was one thing I
was wondering though, is it possible to make the macro check the
subfolders of a directory?

Again any help would be very much appreciated.

Regards,

Mark Pitchford.
 
Hi Mark
what do you mean with 'check the subdirectories'?
not sure what you're trying to achieve :-)
 
Hi Frank,

basically, the folder which the files are in is called Industry Guides
The folder is updated with new documents each week, and in turn, th
new documents are put into a folder of their own namely Batch 1, the
Batch 2, Batch 3 etc.

So I have the main folder, Industry Guides, and then subfolders Batc
1, 2, 3 etc.

Some of the reports may be in Batch 1, some in Batch 2 etc.

When I run the macro the you have written, I need to specify th
directory where the files are. Each time I run it, i may need to do
three or four times changing the directory each time in vb, i
\\industry guides\batch1\, then industry guides\batch2\ etc.

So what I really need is to get the macro to check the folder for th
files but also look in the subfolders of the folder to see if any o
the files are in there too.

Thanks a lot for getting back to me, I really appreciate the help.

Regards,

Mark
 
Hi
one note upfront: I wouldn't use Excel for this kind of operation.
There are better/dedicated solutions available to automate this kind of
batch processing!
one way (without checking subfolders) would be to make the directory a
variable. Lets say you put the directory paths in cell D1 and E1 try

Sub copy_files()
Dim rng as range
dim cell as range
Dim path_src as string
Dim past_dest as string
Dim SrcFilename As String
Dim DestFilename As String

Set rng = selection
path_src = range("D1").value
path_dest = range("E1").value
For each cell in rng
SrcFilename = path_src & cell.Value 'change source path
DestFilename = path_dest & cell.Value 'change destination
path
FileCopy SrcFilename, DestFilename
next
end sub


But again: Try looking for another solution (e.g. check the shareware
archive for scheduler / batch processing -> there are many nearly for
free to automate your task)
 
Back
Top