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
💥Subscribe: / @greggowaffles
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

[@alexrosen8762]

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

[@greggowaffles]

Thanks again for the positive feedback! I’m open to any suggestions

[@user-wd8hi9gp6w]

Amazing!!

[@szewing9038]

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

[@greggowaffles]

Thank you so much!! Will do!!

[@RR-kj3sz]

Thank you!!!

[@MohAboAbdo]

Thank you so much Sir

[@greggowaffles]

No problem! Thanks for watching!!

[@Serg1457]

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

[@raymartbalakit3062]

@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.

[@brianwarner493]

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

[@janineacoba9710]

Hi, how to copy all starting from a desired row

[@gabriellesmith5540]

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

[@burakguner6815]

Hi, have you found out a solution for this?

[@tierratako]

Mine is only opening the first file. Any suggestions?

[@jayant107]

Same here and it's stuck in a loop!