copy multiple files in all folders and subfolders based on excel sheet

[edkawy]

hi, i have found the below code in this link https://www.mrexcel.com/forum/excel...all-folders-subfolders-based-excel-sheet.html

Sub CopyMacro()Set fs = CreateObject("Scripting.FileSystemObject")n = 2Do While Range("A" & n).Value <> ""fs.CopyFile Range("B" & n).Value & "\" & Range("A" & n).Value & ".*", Range("C" & n).Value & "\"n = n + 1LoopEnd Sub

this code required all files to be in one folder. is it possible someone change it so it can also search for subfolders
thanks alot for any help

 

[Trevor_S]

I think this tweak should work, but haven't got access to a computer at the moment to test it - please let me know if it does what you need:

Sub CopyMacro
Set fs = CreateObject("Scripting.FileSystemObject")
n = 2
Do While Range("A" & n).Value <> ""
fs.CopyFile Range("B" & n).Value & "\" & Range("A" & n).Value & ".*", Range("C" & n).Value & "\"
fs.CopyFile Range("B" & n).Value & "\*\" & Range("A" & n).Value & ".*", Range("C" & n).Value & "\"
n = n + 1
Loop
End Sub

Note that this won't build the subfolder structure in the destination folder, so if it finds more than one file with a matching name in different subfolders, one copy version will overwrite the other copy version.

 

[edkawy]

I think this tweak should work, but haven't got access to a computer at the moment to test it - please let me know if it does what you need:

Sub CopyMacro
Set fs = CreateObject("Scripting.FileSystemObject")
n = 2
Do While Range("A" & n).Value <> ""
fs.CopyFile Range("B" & n).Value & "\" & Range("A" & n).Value & ".*", Range("C" & n).Value & "\"
fs.CopyFile Range("B" & n).Value & "\*\" & Range("A" & n).Value & ".*", Range("C" & n).Value & "\"
n = n + 1
Loop
End Sub

Note that this won't build the subfolder structure in the destination folder, so if it finds more than one file with a matching name in different subfolders, one copy version will overwrite the other copy version.

hi , thanks a lot for your prompt reply , i have tried the code and found the error as mentioned below :


fs.CopyFile Range("B" & n).Value & "\*" & Range("A" & n).Value & ".*", Range("C" & n).Value & ""


i really need to search through subfolders.
would you please check it again when u have time

 

[Trevor_S]

I've checked it and unfortunately it isn't as straight-forward as I thought. The * wildcard can be used in filenames, but it can't be put in the filepath to search multiple sub folders.

I think you need a way of looping through the subfolders, and applying a macro similar to my original one, but with an On Error Resume Next line to skip the CopyFile if the file isn't found. Perhaps something like the post by northwolves here: https://www.mrexcel.com/forum/excel...ions-looping-through-directory-structure.html may give a start, but I'm not sure enough about how it works to be able to put my macro into it.

 

[edkawy]

hello, i agree with you , after searching for many days i couldn't found any code for this..it seems its not possible to do this via VBA.. i will look for other method ...thanks a lot for your efforts Trevor_S.