Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all files in a folder
I need to process all the files in a particular folder.
Is there a "For all files in folder Do" construct in VBA? -- Regards, Fred Please reply to newsgroup, not e-mail |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all files in a folder
Hi Fred
Sub OpenWorkbooksInLocation() Application.ScreenUpdating = False Dim i as integer With Application.FileSearch ..NewSearch ..LookIn = "C:\MyFolder\MySubfolder" 'Amend to suit ..SearchSubFolders = False ..FileName = "*.xls" ..Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(FileName:=.FoundFiles(i)) 'Do your stuff here wb.Save wb.Close Next i End With Application.ScreenUpdating = True End Sub Or if it the intention only to list the files, then.... Sub ListWorkbooksInLocation() Application.ScreenUpdating = False Dim i As Integer With Application.FileSearch ..NewSearch ..LookIn = "C:\MyFolder\MySubfolder" 'Amend to suit ..SearchSubFolders = False ..Filename = "*.xls" ..Execute For i = 1 To .FoundFiles.Count ActiveSheet.Cells(i, 1) = .FoundFiles(i) Next i End With Application.ScreenUpdating = True End Sub -- XL2002 Regards William "Fred Smith" wrote in message ... | I need to process all the files in a particular folder. | | Is there a "For all files in folder Do" construct in VBA? | | -- | Regards, | Fred | Please reply to newsgroup, not e-mail | | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all files in a folder
Freed,
Here is some code that opens all workbooks in a folder Sub Open(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) "Fred Smith" wrote in message ... I need to process all the files in a particular folder. Is there a "For all files in folder Do" construct in VBA? -- Regards, Fred Please reply to newsgroup, not e-mail |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all files in a folder
Be careful using the name "Open" for the macro. "Open" is a
reserved word in VBA. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bob Phillips" wrote in message ... Freed, Here is some code that opens all workbooks in a folder Sub Open(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) "Fred Smith" wrote in message ... I need to process all the files in a particular folder. Is there a "For all files in folder Do" construct in VBA? -- Regards, Fred Please reply to newsgroup, not e-mail |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all files in a folder
Thanks so much for your help. .FoundFiles is exactly what I need.
-- Regards, Fred Please reply to newsgroup, not e-mail "Bob Phillips" wrote in message ... Freed, Here is some code that opens all workbooks in a folder Sub Open(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) "Fred Smith" wrote in message ... I need to process all the files in a particular folder. Is there a "For all files in folder Do" construct in VBA? -- Regards, Fred Please reply to newsgroup, not e-mail |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
Loop through folder of workbooks and add rows | Excel Worksheet Functions | |||
Copying all files in a folder to new folder | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
Loop through workbooks in a folder and return the value of cell M43 | Excel Programming |