for-each-next loop
 

Dear experts,
I would like to open through a macro each of the workbooks
that are in a certain folder. (Their number and names can
vary over time).
I wanted to use the FOR EACH... IN... NEXT loop, but it
does not seem to work (I get a "Type mismatch" error
message). Do you know how I could perform this?
Many thanks in advance.
Regards,
Valeria

for-each-next loop
 

Valeria,

This works in XL2000, should also work in 2003 and 97

Sub OpenFiles(Folder As String)
Dim sFolder As String
Dim oWB As Workbook
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = Folder
.SearchSubFolders = False
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set oWB = Workbooks.Open(Filename:=.FoundFiles(i))
'do your stuff here
oWB.Close SaveChanges:=False
Next i
Else
MsgBox "Folder " & sFolder & " contains no required files"
End If
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Valeria" wrote in message
...
Dear experts,
I would like to open through a macro each of the workbooks
that are in a certain folder. (Their number and names can
vary over time).
I wanted to use the FOR EACH... IN... NEXT loop, but it
does not seem to work (I get a "Type mismatch" error
message). Do you know how I could perform this?
Many thanks in advance.
Regards,
Valeria

for-each-next loop
 

Hi Bob,
it works perfectly!
Thanks a lot,
Valeria

-----Original Message-----
Valeria,

This works in XL2000, should also work in 2003 and 97

Sub OpenFiles(Folder As String)
Dim sFolder As String
Dim oWB As Workbook
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = Folder
.SearchSubFolders = False
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set oWB = Workbooks.Open
(Filename:=.FoundFiles(i))
'do your stuff here
oWB.Close SaveChanges:=False
Next i
Else
MsgBox "Folder " & sFolder & " contains
no required files"
End If
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Valeria" wrote in
message
...
Dear experts,
I would like to open through a macro each of the
workbooks
that are in a certain folder. (Their number and names
can
vary over time).
I wanted to use the FOR EACH... IN... NEXT loop, but it
does not seem to work (I get a "Type mismatch" error
message). Do you know how I could perform this?
Many thanks in advance.
Regards,
Valeria


.