Рет қаралды 8,345
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