Excel VBA Macro: Extract Data from All Files in a (User Selected) Folder

Excel VBA Macro: Extract Data from All Files in a (User Selected) Folder

Code:
Sub grab_data_from_files_in_folder()

Dim myPath As String
Dim myFile As String
Dim FldrPicker As FileDialog
Dim sh As Worksheet
Dim i As Integer

Application.ScreenUpdating = False

Set sh = ThisWorkbook.Sheets("Team Summary")
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Please Select Folder"
.AllowMultiSelect = False
.ButtonName = "Confirm!!"
If .Show = -1 Then
myPath = .SelectedItems(1) & "\"
Else
End
End If
End With

With sh
.Cells.ClearContents
.Cells(1, 1) = "Team Name"
.Cells(1, 1).Font.Size = 14
.Cells(1, 1).Font.Bold = True
.Cells(1, 2) = "Total Sales"
.Cells(1, 2).Font.Size = 14
.Cells(1, 2).Font.Bold = True

End With

myFile = Dir(myPath)
i = 2

Do While myFile ＜＞ ""

Workbooks.Open Filename:=myPath & myFile

sh.Cells(i, 1) = ActiveWorkbook.Sheets("Team Sales").Cells(1, 2).Text
sh.Cells(i, 2) = ActiveWorkbook.Sheets("Team Sales").Cells(2, 2).Value

ActiveWorkbook.Close savechanges:=False

myFile = Dir
i = i + 1

Loop

Application.ScreenUpdating = True

End Sub

#excelmacro #excelvba

[alexrosen]

This type of tutorials are extremely useful for many reasons, mostly because it has concrete real life examples attached to it. Big thank you 🙏

[szewing]

Hi Greg I learn a lot from you. Your teaching is precise and clear. Thanks so much! Keep it up!!!

[Serg]

Thanks a lot it is really useful! But if you need to gather date for 10-15 columns is it going to be any different and if every time there will be a new file with data, let's say new team every time, can we keep adding info to already existing values? Thanks a lot

[brianwarner]

Would love to see this extract all data from all word docs in a selected folder & subfolders

[raymartbalakit]

@greggowaffles what if I have different workbooks with different test results? and each sheet of the test result for each file has also unique name? How to make a macro that can extract that.

[janineacoba]

Hi, how to copy all starting from a desired row

[gabriellesmith]

Hi! I got an error message when I tried and it was because of the “Set sh” line

[GabbsOnAir]

How do you fix the code if the sheet names are different every time? For example, instead of "Team Sales" you have "Team Red Sales", "Team Pink Sales", etc

[tierratako]

Mine is only opening the first file. Any suggestions?