asked on

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

Experts,

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

Saurabh Singh Teotia

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
        Next

        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
            Next
        Next
    End If
End Sub

Open in new window

Saurabh...

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

Note that the sub you want to run is "CopyXlsxFiles".

sharepoint0520

ASKER

Patrick.

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.

sharepoint0520

ASKER

Thanks a lot. I figured it out.