Loop through all files in a folder

[Fred Smith]

I need to process all the files in a particular folder.

Is there a "For all files in folder Do" construct in VBA?

 

[William]

Hi Fred

Sub OpenWorkbooksInLocation()
Application.ScreenUpdating = False
Dim i as integer
With Application.FileSearch
..NewSearch
..LookIn = "C:\MyFolder\MySubfolder" 'Amend to suit
..SearchSubFolders = False
..FileName = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(FileName:=.FoundFiles(i))
'Do your stuff here
wb.Save
wb.Close
Next i
End With
Application.ScreenUpdating = True
End Sub

Or if it the intention only to list the files, then....

Sub ListWorkbooksInLocation()
Application.ScreenUpdating = False
Dim i As Integer
With Application.FileSearch
..NewSearch
..LookIn = "C:\MyFolder\MySubfolder" 'Amend to suit
..SearchSubFolders = False
..Filename = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i, 1) = .FoundFiles(i)
Next i
End With
Application.ScreenUpdating = True
End Sub


--
XL2002
Regards

William

(e-mail address removed)

| I need to process all the files in a particular folder.
|
| Is there a "For all files in folder Do" construct in VBA?
|
| --
| Regards,
| Fred
| Please reply to newsgroup, not e-mail
|
|
|

 

[Bob Phillips]

Freed,

Here is some code that opens all workbooks in a folder

Sub Open(Folder As String)
Dim sFolder As String
Dim oWB As Workbook
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = Folder
.SearchSubFolders = False
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set oWB = Workbooks.Open(Filename:=.FoundFiles(i))
'do your stuff here
oWB.Close SaveChanges:=False
Next i
Else
MsgBox "Folder " & sFolder & " contains no required files"
End If
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Be careful using the name "Open" for the macro. "Open" is a
reserved word in VBA.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Fred Smith]

Thanks so much for your help. .FoundFiles is exactly what I need.