Excel VBA to copy and move files

[swaggerbox]

I have text files in C:\MyData folder\08-12-13 folder and I would like to do the following:


copy the text files to C:\ForCompile
move the text files from C:\MyData folder\08-12-13 to C:\MyData folder\08-12-13\COMPILED


Note: The text files are saved in subfolders in C:\MyData folder\08-12-13 e.g. C:\MyData folder\08-12-13\ZaZ, C:\MyData folder\08-12-13\ZaT, C:\MyData folder\08-12-13\AzT, etc. The reason why I am copying files to C:\ForCompile folder is because I am executing another application to reformat these files.
Files are moved to the COMPILED folder to let me know that these files have been processed already. I am only familiar with Excel so that's why I've requested for Excel vba.


Anyone has suggestions on how to do this?

[patel]

not tested

Sub CopySubfoldersFile() 
Dim StrFile As String
   Dim objFSO, destRow As Long
   Dim mainFolder, mySubFolder
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   sFolder = "C:\MyData folder\08-12-13\"
tFolder ="C:\ForCompile\"
   Set mainFolder = objFSO.getfolder(sFolder)
   StrFile = Dir(sFolder & "*.xls*")
For Each mySubFolder In mainFolder.subfolders
         StrFile = Dir(mySubFolder & "\*.xls*")
         Do While Len(StrFile) > 0
       filecopy mySubFolder & "\" & StrFile, tFolder & StrFile
               StrFile = Dir
         Loop
Next
End Sub