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.