PDA

View Full Version : Open all files in selected folder and perform some operation



ExcelBaap
07-24-2017, 09:31 PM
If you want to allow the user to select a folder location, open all files in that folder and perform some operation, this is the code you can use:



Sub TestCode()

'Initializing Variables
Dim MyFolder As String
Dim MyFile As String
Dim out As Worksheet
Dim counter As Long

'Defining Sheets in Variables
Set out = ThisWorkbook.ActiveSheet

'Folder for Selection
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then Exit Sub
sItem = .SelectedItems(1)
End With

MyFolder = sItem
Set fldr = Nothing

'Code to Extract Data from all Excel Files in Selected Folder
counter = 0
MyFile = Dir(MyFolder & "\*.xls*")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile

'Perform the required operation
'------------------------------
'Your operation comes here
'------------------------------

counter = counter + 1

Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir()
Loop

MsgBox "Process complete. Updated " & counter & " files."

End Sub