Copy multiple files to multiple locations

[dizzidecazz]

Hi,

This is my first post to this forum.

I'm trying to work out a script to copy thousands of files from a single (or multiple but it's easy enough to consolidate them) source to specified destination for each row.
Each row contains Columns A:Destination File Path | B:File name |(Optional C:Source File Path)

I came across this thread to move multiple files in a range however it assumes moving all file to a single destination.
http://www.excelforum.com/excel-prog...ml#post4126148

Any help would be very gretaly appreciated.

Damien Cassidy

[Kaper]

The one from cited thread seems to be "almost ready" for you:

Please Login or Register  to view this content.

[dizzidecazz]

Hi Kaper,

Thanks for the response. I'm getting an error where it appears the destination path and file name aren't be defined.

"Copy Error: E:\File Copy Test Source\Testfile1.txt to: \"

This is my test (with both yours and my code)

FilecopierTest.xlsm

...which looks like this:

Destination Path	Filename	Source Path
E:\File Copy Test Destinations\Dest 1	Testfile1.txt	E:\File Copy Test Source
E:\File Copy Test Destinations\Dest 2	Testfile2.txt	E:\File Copy Test Source
E:\File Copy Test Destinations\Dest 3	Testfile3.txt	E:\File Copy Test Source

I don't need to rename the files, just copy them. I modified the code as below but get the same error.
I'm very new to this VBA stuff so I've laid it out step by step. I can't work out why the source path works but the destination doesn't when it's essentially the same thing. .

Any further insights would be very greatly appreciated.

Please Login or Register  to view this content.

Kind regards,

Damien

[Kaper]

Hi Damien,
And what is the name of source file?

[dizzidecazz]

Hi Kaper,

Sorry for the slow reply. This is a work thing and I just enjoyed a long weekend.

In my reply I mentioned that I don't need to change the filename. Having said that, there's plenty of potential scenarios where I would so I'm happy for a solution that does.

I don't see why it would matter but FYI: The spreadsheet is not saved in the same location (or drive) as the source files.

Regards,
Damien

[Kaper]

So ... the file where macro is located is the source?
anyway ... in the code yiu have 4 variabeles

src
fl
dst
rfl

use them to achieve your goal (2 first determine "from where" and 2 last determine "copy to" location and name).

[dizzidecazz]

Hi,

I have updated the script to include optional variable source path and optional variable destination file name. I've renamed the variables to match.

Column A: Source Path (will use last known value if blank - first entry (A2) must have a value
Column B: Source Filename (must have a value)
Column C: Destination Path (must have a value)
Column D: Destination Filename (will use source filename if blank)

Here's the code:

Please Login or Register  to view this content.

Thanks for your help Kaper.
The next step is for it to create the destination path if it doesn't exist...

Regards,
Damien

[Kaper]

See for instance the old thread: http://www.excelforum.com/excel-prog...ady-exist.html