Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
for/next loop | New Users to Excel | |||
For..Next..Loop | Excel Discussion (Misc queries) | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Loop help | Excel Discussion (Misc queries) | |||
getting out of a if loop | Excel Discussion (Misc queries) |