Application.filesearch has disappeared in Excel 2010Here'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
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. If you prefer Video style learning I also have two courses available. also published by O'Reilly. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Get Started Snippets >