How to copy only xlsx files from source Folder/Subfolder to New Destination folder by VBA code?


  I have source Folders with subfolders. And Subfolders has Excel and word files. I would like to copy only excel files to Destination folder. Destination folder auto created with Date or I should create manually.

Can you please help me to to copy all files ?Move_Files.xlsx
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can run the following code which will do what you are looking for..

Sub movefiles()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim FSO As Object, fld As Object
    Dim fname As Object
    Dim sbfol As Object
    Dim fpath As String, xpath As String
    Dim tpath As String

    fpath = "Your folder path here from where you want to copy"
    tpath = "your folder path here to where you want to copy"
    If Right(fpath, 1) <> "\" Then fpath = fpath & "\"
    If Right(tpath, 1) <> "\" Then tpath = tpath & "\"

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fld = FSO.getfolder(fpath)
    If FSO.folderExists(fld) Then
        For Each fname In fld.Files
            If Mid(fname.Name, InStrRev(fname.Name, ".") + 1) = "xlsx" Then

                xpath = fname.Path

                FSO.movefile Source:=xpath, Destination:=tpath
            End If

        For Each sbfol In FSO.getfolder(fpath).subfolders
            For Each fname In sbfol.Files
                If Mid(fname.Name, InStrRev(fname.Name, ".") + 1) = "xlsx" Then

                    xpath = fname.Path

                    FSO.movefile Source:=xpath, Destination:=tpath
                End If
    End If
End Sub

Open in new window

Patrick MatthewsCommented:
You want to copy the files;
You may have multiple layers of subfolders; and
You want to have a GUI to select the source and destination folders (and to create a new destination if needed)

then try this code.  It uses recursion to drill into subfolders until none are left.

Option Explicit

Private Function GetDirectory2(Optional Msg As String = "Select Folder:") As String
    ' Use this version when you want to be able to create a new directory and
    ' have the function return that path
    Dim objShell As Object 'Shell32.Shell
    Dim objFolder As Object 'Shell32.Folder
    Dim objFolderItem As Object 'Shell32.FolderItem
    GetDirectory2 = ""
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.BrowseForFolder(0, Msg, 0, 0)
    If (Not objFolder Is Nothing) Then
        Set objFolderItem = objFolder.Self
        If (Not objFolderItem Is Nothing) Then
            GetDirectory2 = objFolderItem.path
        End If
    End If
    Set objFolderItem = Nothing
    Set objFolder = Nothing
    Set objShell = Nothing
End Function

Sub CopyXlsxFiles()
    Dim SourceDir As String
    Dim DestDir As String
    SourceDir = GetDirectory2("Select source folder")
    If SourceDir = "" Then Exit Sub
    DestDir = GetDirectory2("Select destination folder")
    If DestDir = "" Then Exit Sub
    CopyAFolder SourceDir, DestDir
End Sub

Sub CopyAFolder(SourcePath As String, DestPath As String)
    Dim fso As Object
    Dim fld As Object
    Dim sf As Object
    Dim fil As Object
    Dim NewDestPath As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(SourcePath)
    For Each fil In fld.Files
        If LCase(fil.Name) Like "*.xlsx" Then
            fil.Copy DestPath & "\" & fil.Name
        End If
    For Each sf In fld.SubFolders
        NewDestPath = DestPath & "\" & sf.Name
        If Not fso.FolderExists(NewDestPath) Then
            fso.CreateFolder NewDestPath
        End If
        CopyAFolder sf.path, NewDestPath
    Set fil = Nothing
    Set sf = Nothing
    Set fld = Nothing
    Set fso = Nothing
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
Note that the sub you want to run is "CopyXlsxFiles".
sharepoint0520Author Commented:

 It;s very good that i am able to select folder but i want only files from folder. When i select Source Folders , it will copy whatever that folder has including subfolders. I just want xls files from each sub folder and copy to Destination folder.
sharepoint0520Author Commented:
Thanks a lot. I figured it out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.