When managing projects I use a few different off-the-shelf products.\u00a0 But, it always seems there is some task I need to do that can’t be done efficiently with these tools so I make sure to include MS Excel (Office 365) in my suite of project management utilities.\u00a0 VBA macros within Excel give me the power to complete any custom tasks I require.<\/p>\n
I needed to copy a set of folders (an entire recursive tree) to a new location.\u00a0 VBA does not natively include this feature.\u00a0 I searched the web for solutions but it seemed all were either out-of-date (non functional) or simply incomplete (never worked!).\u00a0 So, I thought I’d share my solution built on the VBA scripting.filesystemobject. Hopefully it will help others. Here it is …<\/p>\n
\r\nPublic Sub TestCopyFiles()\r\n ' Call the recursive copy operation\r\n CopyFiles "c:\\temp\\a", "c:\\temp\\b"\r\nEnd Sub\r\n\r\nPublic Sub CopyFiles(ByVal strPath As String, ByVal strTarget As String, Optional ByVal basePath As String, Optional ByVal baseTarget As String)\r\n If basePath = "" Then basePath = strPath\r\n If baseTarget = "" Then baseTarget = strTarget\r\n Set FSO = CreateObject("scripting.filesystemobject")\r\n ' First loop through files in this folder\r\n For Each nextFile In FSO.getfolder(strPath).Files\r\n fullTargetPath = strTarget + "\\" + nextFile.Name\r\n mkdir (strTarget)\r\n ' Copy one file\r\n FileInFromFolder.Copy fullTargetPath\r\n Next\r\n ' Next loop through folders in this folder\r\n For Each nextFolder In FSO.getfolder(strPath).SubFolders\r\n nextStrTarget = baseTarget + Right(nextFolder, Len(nextFolder) - Len(basePath))\r\n mkdir (nextStrTarget)\r\n CopyFiles nextFolder.Path, nextStrTarget, basePath, baseTarget\r\n Next\r\nEnd Sub\r\n\r\nPrivate Sub mkdir(folder As String)\r\n ' Create a folder if it doesn't exist\r\n Set FSO = CreateObject("scripting.filesystemobject")\r\n On Error Resume Next\r\n FSO.CreateFolder (folder)\r\n On Error GoTo 0\r\nEnd Sub\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"When managing projects I use a few different off-the-shelf products.\u00a0 But, it always seems there is some task I need to do that can’t be done efficiently with these tools so I make sure to include MS Excel (Office 365) in my suite of project management utilities.\u00a0 VBA macros within Excel give me the power … Continue reading Copying files recursively in MS Excel with VBA<\/span>