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
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.
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines