run macro for all files in the directory

  • Thread starter Thread starter igor
  • Start date Start date
I

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
 
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
 
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




.
Click to expand...
 
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
 
Back
Top