write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Wednesday 15 June 2011

Separate files based on extension (.xls,.xlsx,.jpg,.pdf,.txt,.pptx)

If you want to separate files base on their extension use below code, enter the path of your folder,
and path where you want to copy the files
steps :
1. Press Alt + F11 or From Developer tab click on visual basic
2. Go to insert select module
3. copy below code in module

Public F As Object, d As Object, sw As Object
Sub test()
    Dim Fso As Object
    Set sw = CreateObject("Scripting.FileSystemObject")
    Set Fso = CreateObject("Scripting.FileSystemObject")
    'Enter path of folder
    Set RootFolder = Fso.GetFolder("C:\Users\MAHESH\Desktop\Temp\")
    FolderRead RootFolder
End Sub
Sub FolderRead(ByRef myFolder)
For Each F In myFolder.Files
    Var = F.Path
    If Right(F.Name, 4) = ".xls" Or Right(F.Name, 5) = ".xlsx" Then
    'enter the path where you want to copy file
    sw.copyFile F.Path, "C:\Users\MAHESH\Desktop\Temp\EXCEL\"
    End If
If Right(F.Name, 5) = ".jpg" Then
sw.copyFile F.Path, "C:\Users\MAHESH\Desktop\Temp\JPG\"
End If
If Right(F.Name, 4) = ".pdf" Then
sw.copyFile F.Path, "C:\Users\MAHESH\Desktop\Temp\PDF\"
End If
If Right(F.Name, 5) = ".txt" Then
sw.copyFile F.Path, "C:\Users\MAHESH\Desktop\Temp\TXT\"
End If
If Right(F.Name, 5) = ".pptx" Then
sw.copyFile F.Path, "C:\Users\MAHESH\Desktop\Temp\PPT\"
End If
Next F
For Each d In myFolder.SubFolders
FolderRead d
Next d
End Sub

No comments:

Post a Comment