open all files in a folder and ...
 

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

open all files in a folder and ...
 

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


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


.

open all files in a folder and ...
 

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

open all files in a folder and ...
 

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

walt wrote:

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

--

Dave Peterson

open all files in a folder and ...
 

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

open all files in a folder and ...
 

what do you mean rewarded? how?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!