Looping through files in a directory

Here’s an alternative snippet for how to iterate through a collection of files. Lets say we want to look at every workbook in the current directory, except the currently open one, and show each sheet name we encounter. 
Here’s how

Public Sub fileLoop()
    Dim sPath As String, ws As Variant
    ' this will loop though every file in same directory as current book
    ' except the current one
    sPath = ThisWorkbook.Path
    Dim fs As Variant, f As Variant, f1 As Variant, fc As Variant
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(sPath)
    Set fc = f.Files

    For Each f1 In fc
        If f1 <> ThisWorkbook.FullName Then
            With Workbooks.Open(Filename:=f1, ReadOnly:=True)
                ' do something .. show all the sheetnames
                For Each ws In .Sheets
                    Debug.Print ws.name
                Next ws
                .Close False
            End With
        End If
    Next f1
End Sub

For more tips like this see Get Started Snippets
In the meantime why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.