Solved: Loop through user specified folder

[kathyb0527]

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]

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]

Thanks XLD. Is there any way the user can select the folder to loop through?

[Bob Phillips]

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