VBA macro to copy files from one folder (and all it's subfolders, and their subfolders, and their subfolders with an exception....) to another folder?

[Bering]

Hello, I found this code in an old post which is almost perfect for what I am trying to achieve.

Unfortunately, I don't understand what the letters in the code refer to... Can the code be modify to exclude subfolders with a specific names?
In my case I want to copy to another location a set of folder which may contain many subfolders and so on. At some point there will be subfolders for each year starting from 2020. What I want to achieve is copy the entire folder structure excluding all years except for the current one.

VBA macro to copy files from one folder (and all it's subfolders, and their subfolders, and their subfolders....) to another folder?

Sub CopyFiles()

    Dim sPathSource As String, sPathDest As String, sConsoleCmd As String
   
    sPathSource = "C:\SourceFolder\*.*"
'    sPathSource = "C:\SourceFolder\*.pdf"
'    sPathSource = "C:\SourceFolder\*.xls?"
    sPathDest = "Z:\DestinationFolderTree\SomeFolder\EndpointFolder"
   
    If Not Right(sPathDest, 1) = "\" Then sPathDest = sPathDest & "\"
     sConsoleCmd = "cmd.exe /k xcopy " & """" & sPathSource & """" & " " & """" & sPathDest & """" & " /s/i/y/r/k"
    Shell sConsoleCmd, 1    ' << change 6 into 1 to see what's going on
End Sub

Many thanks!

 

[Micron]

an example of how a folder name to be ignored fits into or gets added to this string would help.
"Z:\DestinationFolderTree\SomeFolder\EndpointFolder"
The answer is probably to wrap the portion that does the copying into an IF block but I can't provide an example with the information given. I don't think it's possible to use command line switches. If you want to know more about those, here's a link.
There are other methods for this using vba, such as using File Scripting Object (FSO) or vba Name function. I mention that because running cmd is sometimes not allowed by IT groups.

 

[Bering]

an example of how a folder name to be ignored fits into or gets added to this string would help.
"Z:\DestinationFolderTree\SomeFolder\EndpointFolder"
The answer is probably to wrap the portion that does the copying into an IF block but I can't provide an example with the information given. I don't think it's possible to use command line switches. If you want to know more about those, here's a link.
There are other methods for this using vba, such as using File Scripting Object (FSO) or vba Name function. I mention that because running cmd is sometimes not allowed by IT groups.

Thanks for your reply!

So, in my path "Z:\DestinationFolderTree\" at the moment I have a total of 17 folders.
Inside each of these folders there are many subfolder.

Inside each of these subfolders there are sub-subfolders for each year starting from 2021.

"Z:\DestinationFolderTree\Folder1\subfolder1\202X"

I'd like to copy the entire folder structure into a different path excluding previous years, hence only keeping the sub-subfolders relating to 2023.

I have been struggling with this all day :-\

 

[Micron]

IMO you cannot do this efficiently by using cmd and xcopy. If in vba you looped over a directory and got all parent folders that had no subfolders where the folder name did not contain any year prior to 2023 then you could pass only that directory to xcopy. But if you were going to do that you might as well drop the whole idea of using xcopy and use vba instead. If I search on "vba loop over directories and subfolders" I get 60 results. Maybe you should look into that and see what you think. In the end, you will need a recursive sub or function, and while I didn't explore any of those links today I have no doubt that's what they'll contain.

 

[Bering]

IMO you cannot do this efficiently by using cmd and xcopy. If in vba you looped over a directory and got all parent folders that had no subfolders where the folder name did not contain any year prior to 2023 then you could pass only that directory to xcopy. But if you were going to do that you might as well drop the whole idea of using xcopy and use vba instead. If I search on "vba loop over directories and subfolders" I get 60 results. Maybe you should look into that and see what you think. In the end, you will need a recursive sub or function, and while I didn't explore any of those links today I have no doubt that's what they'll contain.

Thanks, indeed I am also getting memory outage messages… I will try the vba route