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

💥Subscribe: [ Ссылка ]

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

свернуть