for-each-next loop

V

Valeria

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
 
B

Bob Phillips

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

Guest

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)




.
Click to expand...
 
Top