run macro for all files in the directory
 

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

run macro for all files in the directory
 

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" wrote in message
...
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

run macro for all files in the directory
 

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

"igor" wrote in message
...
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


.

run macro for all files in the directory
 

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/...ExcelFAQID=204


--

Regards,


Bill Lunney
www.billlunney.com

"igor" wrote in message
...
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