code to loop through all workbooks in a folder VB

Question

Hi, I have a number of excel (.xls) stored in a folder in a local drive. I need to do some process to every file in this folder. What is the code that will

1. loop through every file
2. open the file
3. Do some processing and then Save & close the file
4. move the file to another folder after processing

To be more clear, I want go over every file and do processing to it. After finishing a file, go to another file and so till the end of all the files in the folder. I do have the code for the processing; I just need to know the code that will loop through the files and move then to another folder.

Thanks for your help in advance,

Answer 1

What you need is a recursive function that iterates over the tree that represents a file system. It means to iterate over all the childs of some 'parent folder'. I send you a function that does something similar, to the one you need (this is currently in usage). This function deletes all the empty folders given a parent folder.

Public Function gf_DeleteEmptyFolder(path As String) As Boolean
    On Error GoTo Error_Handler    
    Dim fso_folder As Scripting.Folder, sub_folder As Scripting.Folder    
    If g_FSO.FolderExists(path) Then
        Set fso_folder = g_FSO.GetFolder(path)
        '-- eliminates de folder only if is empty
        If 0 = fso_folder.Files.Count And 0 = fso_folder.SubFolders.Count Then
            Call g_FSO.DeleteFolder(path, False)
        '-- recursively calls the function
        Else
            For Each sub_folder In fso_folder.SubFolders
                Call gf_DeleteEmptyFolder(sub_folder.path)
            Next
        End If
    End If
    gf_DeleteEmptyFolder = True    
    Exit Function
'~~~ on error
Error_Handler:
    gf_DeleteEmptyFolder = False
End Function

If your files are stored in a simple folder, then you can use the following code to iterate each file.

Public Sub fsoProcessFilesInFolder(sFolder As String)        
    Dim fso As Scripting.FileSystemObject, fld As Scripting.Folder, fil As Scripting.File    
    Set fso = New FileSystemObject
    Set fld = fso.GetFolder(sFolder)    
    For Each fil In fld.Files
        '--- add code to process your files
    Next fil
End Sub

Answer 2

Here's the easy VBA object way to do it:

Dim fs As FileSearch
Dim i As Integer
Dim wbk As Workbook

Set fs = Application.FileSearch

With fs
    .LookIn = ThisWorkbook.Path
    .FileName = "*.xls"
    For i = 1 to .Execute()
        Set wbk = Workbooks.Open(.FoundFiles(i))
        ''//DO STUFF HERE
        wbk.Close(SaveChanges:=True)
    Next i
End With

In VB6 you have three options, as shown in the following KB articles:

How to Search Directories to Find or List Files
HOW TO: Recursively Search Directories by Using FileSystemObject

Answer 3

Following code will read xlsx/xls files from given folder neglecting other files and iterate through each item. You can use it for any set of extensions and filters.

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(folderPath)

Set objFiles = objFolder.Files

'Iterate through the files in the folder

For Each Item In objFiles

If LCase(Right(Item.Name, 5)) = ".xls" Or LCase(Right(Item.Name, 4)) = ".xlsx" Then

 ''''''Do Stuffs Here''''''

End If

Next