Macro to extract Excel files within sub-folder in a main folder

[howard]

I have a folder called C:\downloads

I downloaded a zip file and when unzipping the file, it created a sub-folder "Case Studies" and then for each individual workbook another sub-folder was created and within each of the sub-solders there are the excel workbooks



I would like a macro to extract excel workbooks from all the sub-folders within C:\downloads and paste them in C:\downloads


It would be appreciated if someone could assist me

 

[Norie]

Try this.

Option Explicit
Dim FSO As Object

Sub GetAllDownloadExcelFiles()
Dim strStartFolder As String

    strStartFolder = "C:\test"

    Set FSO = CreateObject("Scripting.FileSystemObject")

    MoveFiles FSO.GetFolder(strStartFolder)

End Sub

Sub MoveFiles(Folder)
Dim fld As Object
Dim fl As Object

    For Each fld In Folder.SubFolders
        MoveFiles fld
    Next

    For Each fl In Folder.Files
        If fl.Name Like "*xls*" Then
            Debug.Print fl.Name
            FSO.MoveFile fl.Path, "C:\TestDownload\" & fl.Name
        End If
    Next

End Sub

 

[howard]

Thanks for the help


I have amended strStartFolder = "C:\test" to my folder

What do I change code below to ?

 FSO.MoveFile fl.Path, "C:\TestDownload\" & fl.Name [/code[

 

[Norie]

You need to change "C:\TestDownload" to the path for the folder where you want to move the files to.

 

[howard]

Hi Norie

When the files were unzipped they were extracted in C:\Users\Downloads\Excel Case Studies and then within the folder Excel Case Studies there were sub-folders series 01, series 02, series 03 etc

I tried to amend your code as follows but I get path not found

 strStartFolder = "C:\Users\Downloads\Excel Case Studies\series*.*\"

I want the files extracted to

 FSO.MoveFile fl.Path, "C:\Users\Downloads" & fl.Name

It would be appreciated if you can assist me

 

[fhqwgads]

can you not just do a windows explorer search in the downloads folder of .xls files, select all results, and then cut paste them? or do you specifically need a macro?

 

[Norie]

The code I posted will find Excel files in the folder specified in strStartFolder and all the sub-folders of that folder, so no need for 'series*.*'.

 

[howard]

Thanks for letting me knw