open all files in a folder and ...

  • Thread starter Thread starter walt
  • Start date Start date
W

walt

My macro works fine in reading the content of one file into a summary file.
But i don't want to start the macro for every single file in a specific
folder (selected by the user). Is there a way to tell Excel: open first
file in folder, run macro, close file, open next one, run macro, close
file.... until all files are done?

The actual opening routine looks like that:

Dim myFileName As Variant
Dim myWkbk As Workbook

myFileName = Application.GetOpenFilename("Excel files, *.xls")
If myFileName = False Then
Exit Sub
End If

Application.ScreenUpdating = False

Set myWkbk = Workbooks.Open(Filename:=myFileName)


TIA Walt
 
see code below,
that should do it

Sub WorkWithFiles()
'Dim As Long
Dim wkbk As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "c:\my folder"
.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
' paste macro here

wkbk.Close SaveChanges:=False 'change to true to save
changes
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
 
Thank you, it works fine! Only one last thing to do now :o)

This .LookIn folder should be a variable location... Any ideas? Well, i have
the whole day for trying ;o)

Walt
 
xl2002? Then look at:
Application.FileDialog(msoFileDialogFolderPicker)
in the help.

If before, then Jim Rech has a BrowseForFolder routine at:
http://www.BMSLtd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

(You did mean that you wanted the user to select the folder???)
 
Back
Top