Public Sub GetDataFiles(strPath As String, SheetName As String, DataRange As String, Col As Long, Target As Range)
Static FSO As Object, ObjFile As Object
Dim Res(), Arr(), i As Long, j As Long, k As Long, Cols As Long
Dim FilePath As String, Sht As String, Data As String
If Excel4MacroSheets.Count = 0 Then
Application.Excel4MacroSheets.Add.Name = "Temp"
Sheets("Temp").Visible = 2
End If
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
For Each ObjFile In FSO.GetFolder(strPath).Files
If FSO.GetExtensionName(ObjFile) Like "xls*" Then
If Left(ObjFile.Name, 2) <> "~$" Then
If ObjFile.Name <> ThisWorkbook.Name Then
Sht = SheetName & "'!" & DataRange
FilePath = "='" & FSO.GetParentFolderName(ObjFile) _
& "\[" & FSO.GetFilename(ObjFile) & "]" & Sht
With Sheets("Temp").Range(DataRange)
.FormulaArray = FilePath
Res = .Value
Cols = .Columns.Count
.ClearContents
End With
ReDim Preserve Arr(1 To 65536, 1 To Cols)
For i = 1 To UBound(Res, 1)
If Res(i, Col) <> Empty Then
k = k + 1
For j = 1 To UBound(Res, 2)
Arr(k, j) = Res(i, j)
Next
End If
Next
End If
End If
End If
Next
If k Then Target.Resize(k, UBound(Res, 2)).Value = Arr
Set FSO = Nothing
End Sub
''Chay Sub Sau
Private Sub Main()
Dim Path As String, Sht As String, Data As String
Path = ThisWorkbook.Path ''duong dan tong hop File
Sht = "" ''Ten Sheet can Tong Hop
Data = ("A2:B1000") ''Vung du lieu can lay
ActiveSheet.UsedRange.ClearContents
GetDataFiles Path, Sht, Data, 2, [A2] ''2 = Cot Loc theo dieu kien co du lieu
End Sub
