Opening multiple files

[nikneven]

I was wondering if there was a way to write a macro so that it goes to a folder ( G:\2007\9-07 ) and then opens all excel files in that folder and its sub folders that had the word Payroll in the name?

 

[jindon]

try

Private myList
Private n As Long

Sub SearchFiles()
     ListAllBooks "G:\2007\9-07", "*.xls", "*Payroll*"
     MsgBox IIf(n > 0, Join(myList,vbLf), "Not found")
     If n > 0 Then OpenFiles
End Sub

Private Sub OpenFiles()
Dim i As Long
For i = 1 To UBound(myList)
     Workbooks.Open(myList(i))
Next
End Sub

Private Sub ListAllBooks(myDir As String, myFileName As String, subName As String)
Dim fso As Object, myFile As Object, myFolder As Object
Set fso = CreateObject("FileSystemObject")
For Each myFile In fso.GetFoler(myDir).Files
     If myFile.Name Like myFileName Then
          n = n + 1 : ReDim Preserve myList(1 To n)
          myList(n) = myDir & "\" & myFile.Name
     End If
Next
For Each myFolder In fso.GetFoler(myDir).Subfolders
     If myFolder.Name Like subName Then
          ListAllBooks myDir & "\" & myFolder.Name, myFileName, subName
     End If
Next
End Sub

 

[nikneven]

Wonderful! Thank you so much, you just knocked 30 minutes off my morning routine!