View Full Version : Solved: Loop through user specified folder
kathyb0527
08-14-2009, 02:41 PM
I'm sure this has been asked and answered many times, but I can't seem to find something that will work for me. I want to create an addin that that loops through files in a folder and copies data to another worksheet. The reason I'm making this an addin is because multiple people will be using it and both the destination workbook and the source folder will change (there is no "Master" workbook).
Have a great weekend and Thanks as always!
Bob Phillips
08-15-2009, 05:08 AM
Sub LoopFolders()
Dim oFSO As Object
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("c:\MyTest")
For Each file In Folder.Files
If file.Type Like "Microsoft*Excel*Worksheet*" Then
Workbooks.Open Filename:=file.Path
'do something with it
ActiveWorkbook.Close savechanges:=False
End If
Next file
Set oFSO = Nothing
End Sub
kathyb0527
08-17-2009, 08:28 AM
Thanks XLD. Is there any way the user can select the folder to loop through?
Bob Phillips
08-17-2009, 11:23 AM
Of course
Sub LoopFolders()
Dim oFSO As Object
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr
Set oFSO = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
Set Folder = oFSO.GetFolder(.SelectedItems(1))
End With
For Each file In Folder.Files
If file.Type Like "Microsoft*Excel*Worksheet*" Then
Workbooks.Open Filename:=file.Path
'do something with it
ActiveWorkbook.Close savechanges:=False
End If
Next file
Set oFSO = Nothing
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.