Copy files from one folder to another folder based on inputs from a table

Hi all,

May I have your help to add below functions to the vba please?


1. if source or destination folder do not exist, column D will remark" no source / destination file found".
2. For other situations, when a file was copied successfully, the corresponding cell in column D will automatically remark " transferred", however, when copy failed, the corresponding cell in column D will automatically remark "failed"
3. Where should I put on error resume next if I want the program to run through the last row?

tab1.JPG



****************************************

Dim fso
Dim source_file As String
Dim source_folder As String
Dim destination_folder As String
Dim lastrow As Integer

lastrow = ActiveSheet.UsedRange.Rows.Count

For i = 2 To lastrow
source_file = Cells(i, "a")
source_folder = Cells(i, "b")
destination_folder = Cells(i, "c")

Set fso = CreateObject("scripting.filesystemobject")
fso.CopyFile (source_folder & source_file), destination_folder, True
Cells(i, "d") = "successfully transferred"

Next i

End Sub
*******************************************

Originally Posted by kentnood

1. if source or destination folder do not exist, column D will remark" no source / destination file found".

Hi,

why not better creating the destination folder if it does not exist ?
Easy with the Windows function MakeSureDirectoryPathExists as you can see in threads within this forum ...

Originally Posted by sintek

....Wrong Thread....

Why Syntec? It's repeated?...

Hello
One way to do it:

Please Login or Register  to view this content.

And comment if you change your mind and a new folder needs to be created.

Originally Posted by beyond Excel

Hello
One way to do it:

Please Login or Register  to view this content.

And comment if you change your mind and a new folder needs to be created.

Thank you very much, beyond Excel!

This is what I wanted. And yes, I want to create the new destination folders too. Could you advise again, please?

See post #3 ...

Originally Posted by kentnood

Thank you very much, beyond Excel!

This is what I wanted. And yes, I want to create the new destination folders too. Could you advise again, please?

In the attached variant, if the destination path does not exist, the folder is created first, and then the file is copied:

Please Login or Register  to view this content.

Thank you all for your input!