for-each-next loop

  • Thread starter Thread starter Valeria
  • Start date Start date
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
 
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)
 
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...
 
Back
Top