run macro for all files in the directory

[igor]

Hi,

I am trying to figure out how to run a macro for all the
files in the directory without specifying each individual
file.
I want the macro to open 1st file, run macro, close the
first file; then open the second file and do the same
thing for all other files.

Please help with the code...

Thank you
Igor

 

[Tom Ogilvy]

Sub WorkWithFiles()
Dim as long
Dim wkbk as Workbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyFolder"
.SearchSubFolders = False
.FileName = ".xls"
' .FileType = msoFileTypeAllFiles
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
set wkbk = Workbooks.Open(.Foundfiles(i))
' work with the wkbk reference
' macro1
wkbk.Close SaveChanges:=False
Else
MsgBox "There were no files found."
End If
End With
End Sub

Regards,
Tom Ogilvy

 

[igor]

thank you veeeeeeeeery much

-----Original Message-----
Sub WorkWithFiles()
Dim as long
Dim wkbk as Workbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyFolder"
.SearchSubFolders = False
.FileName = ".xls"
' .FileType = msoFileTypeAllFiles
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
set wkbk = Workbooks.Open(.Foundfiles(i))
' work with the wkbk reference
' macro1
wkbk.Close SaveChanges:=False
Else
MsgBox "There were no files found."
End If
End With
End Sub

Regards,
Tom Ogilvy




.

 

[Bill Lunney]

Option Explicit


Public Sub ReadExcelFiles(FolderName As String)
Dim FileName As String

' Add trailing \ character if necessary
'
If Right(FolderName, 1) <> "\" Then FolderName = FolderName & "\"

FileName = Dir(FolderName & "*.xls")

Do While FileName <> ""
Workbooks.Open (FolderName & FileName)

' Do whatever workbook manipulation here

Workbooks(FileName).Close
FileName = Dir()
Loop
End Sub

Public Sub test()
ReadExcelFiles ("c:\temp\test")
End Sub

http://www.billlunney.com/Excel/FAQ/DisplayFAQ.ascx?ExcelFAQID=204


--

Regards,


Bill Lunney
www.billlunney.com