PDA

View Full Version : Excel VBA to copy and move files



swaggerbox
08-17-2013, 02:05 AM
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
08-17-2013, 04:57 AM
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