Bài viết: Tổng hợp dữ liệu từ nhiều file excel vào 1 file không cần mở file

ndu96081631

Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
29,180
Thích
50,562
#1
Bài toán đặt ra như sau:

Mình lúc nào cũng sẽ có 5 file dữ liệu (mỗi file khoảng 2000-5000 dòng dữ liệu), download trực tiếp hàng ngày từ server với định dạng định sẵn dưới định dạng .xls (số cột và vị trí cột định sẵn theo mẫu đính kèm là các file CA1, CA2, CA3, CA4, CA5). Mình cần tổng hợp lại 5 file vào 1 file duy nhất (như mẫu đính kèm) trong đó du liệu của các file CA1, CA2, CA3, CA4, CA5 sẽ nối tiếp nhau ghep vào 1 sheet theo đúng cột tương ứng. Để tổng hợp đc mà k cần mở cả 5 file lên là tốt nhất (như kiểu paste link và có linh external data vậy).

Bài toán trên chỉ là một trong những nhu cầu của rất nhiều người về việc làm thế nào để tổng hợp dữ liệu từ nhiều file Excel khác nhau vào chung một file.



Để làm được điều này, bạn hãy sử dụng đoạn code sau.

Mã:
Function GetData(ByVal FileName As String, ByVal SheetName As String, ByVal RangeAddress As String, _
            ByVal HasTitle As Boolean, ByVal UseTitle As Boolean)
          
  Dim rsCon As Object, rsData As Object, cat As Object, tbl As Object
  Dim tmpArr, Arr()
  Dim szConnect As String, szSQL As String, tmp As String
  Dim lCount As Long, lR As Long, lC As Long, lVer As Long
  lVer = Val(Application.Version)
  Set rsCon = CreateObject("ADODB.Connection")
  Set rsData = CreateObject("ADODB.Recordset")
  Set cat = CreateObject("ADOX.Catalog")
 
  If lVer < 12 Then
    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";" & _
                "Extended Properties=""Excel 8.0;HDR=" & IIf(HasTitle, "Yes", "No") & """;"
  Else
    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";" & _
                "Extended Properties=""Excel 12.0;HDR=" & IIf(HasTitle, "Yes", "No") & """;"
  End If
  If SheetName = "" Then
    Dim Dbs  As Object, db As Object
    Set Dbs = CreateObject("DAO.DBEngine." & IIf(lVer < 12, "36", "120"))
    Set db = Dbs.OpenDatabase(FileName, False, False, "Excel 8.0;")
    tmp = db.TableDefs(0).Name
    tmp = Replace(tmp, " ", "?")
    tmp = Replace(tmp, "'", " ")
    tmp = WorksheetFunction.Trim(tmp)
    tmp = Replace(tmp, " ", "'")
    tmp = Replace(tmp, "?", " ")
    SheetName = tmp
    db.Close
    Set Dbs = Nothing: Set db = Nothing
  End If
  If Right(SheetName, 1) <> "$" Then SheetName = SheetName & "$"
  rsCon.Open szConnect
  cat.ActiveConnection = rsCon
 
  szSQL = "SELECT * FROM [" & SheetName & RangeAddress & "];"
  rsData.Open szSQL, rsCon, 0, 1, 1
  tmpArr = rsData.GetRows
  ReDim Arr(UBound(tmpArr, 2) - UseTitle, UBound(tmpArr, 1) + 1)
  If UseTitle Then
    For lC = LBound(tmpArr, 1) To UBound(tmpArr, 1)
      Arr(0, lC) = rsData.Fields(lC).Name
    Next
  End If
  For lR = LBound(tmpArr, 2) To UBound(tmpArr, 2)
    For lC = LBound(tmpArr, 1) To UBound(tmpArr, 1)
      Arr(lR - UseTitle, lC) = tmpArr(lC, lR)
    Next
  Next
  rsData.Close: Set rsData = Nothing
  rsCon.Close: Set rsCon = Nothing
  GetData = Arr
End Function

Sub Main()
  Dim vFile, FileItem, aRes, Target As Range
  Dim FileName As String, SheetName As String, RangeAddress As String
  On Error Resume Next
  vFile = Application.GetOpenFilename("Excel File, *.xls; *.xlsx; *.xlsm", , , , True)
  If TypeName(vFile) = "Variant()" Then
    SheetName = "Sheet1": RangeAddress = "[B]A8:V10000[/B]"
    For Each FileItem In vFile
      FileName = CStr(FileItem)
      If UCase(FileName) <> UCase(ThisWorkbook.FullName) Then
        aRes = GetData(FileName, SheetName, RangeAddress, False, False)
        If IsArray(aRes) Then
          Set Target = Sheet1.Range("A60000").End(xlUp).Offset(1)
          Target.Resize(UBound(aRes, 1) + 1, UBound(aRes, 2) + 1).Value = aRes
        End If
      End If
    Next
    MsgBox "Done!"
  End If
End Sub
Cách dùng:
- Cho code trên vào Module
- Chạy sub Main
- Cửa sổ Open File hiện ra, dùng chuột chọn file đầu, bấm giữ phím Shift rồi còn file cuối ---> Bấm nút Open
- Chờ trong giây lát, khi MsgBox hiện ra báo hiệu hoàn tất, kiểm tra lại dữ liệu trong file hiện hành xem đã cập nhật chưa
-------------
Lưu ý: File TongHop phải được lưu theo định dạng XLSM (nếu lưu bằng XLSX sẽ mất sạch code). Ngoài ra, bạn cũng lưu ý đoạn tô đậm A8:V10000 tô đậm trên chính là địa chỉ lấy dữ liệu. Nếu dữ liệu của bạn bắt đầu từ A2 đến F100 chẳng hạn, bạn có thể sửa thành A2:F100 để bảo đảm sự chính xác.

Chúc bạn thành công!

Một số bài viết có liên quan:
1/ Làm cách nào để ghi chú hiệu quả trong VBA?
2/ Conditional Formatting cho biểu đồ bằng VBA
3/ Khi nào nên sử dụng Msgbox, Inputbox và Userform?
4/ 8 thủ thuật trong VBE bạn nên biết
5/ Kích hoạt macro từ nút bấm ngoài bảng tính
6/ Làm thế nào để thay thế các chữ OK, CANCEL,... nhàm chán của Msgbox
7/ Giới thiệu VBA trong Excel
8/ Viết code để nhìn thấy ai là người cập nhật bảng tính của bạn lần gần đây nhất
9/ 4 cách sử dụng Immediate Window trong VBA hiệu quả hơn
10/ 3 gợi ý nhỏ mang lại thành công trong khai báo biến trong VBA
 

File đính kèm

Chỉnh sửa lần cuối bởi điều hành viên:

khieukienag

Thành viên thường trực
Tham gia ngày
29 Tháng tư 2008
Bài viết
203
Thích
116
#2
Cảm ơn sự chia sẻ.
Xin được chia sẻ tiếp: Tổng hợp số liệu 5 file vào 1 file tổng hợp. Cả 6 file nầy có dòng và cột giống nhau. Xin cám ơn
 

hoanganhdl

Thành viên hoạt động
Tham gia ngày
10 Tháng hai 2009
Bài viết
134
Thích
74
#3
Đối với người biết VBA thì có thể chế cháo thêm một chút là copy sheet tổng hợp sang file mới và lưu dạng .xls, chỉ cần lưu 1 file .xlsm như 1 tool dùng để tổng hợp dữ liệu thôi.
 

quanluu1989

Thành viên gắn bó
Tham gia ngày
11 Tháng tư 2013
Bài viết
1,838
Thích
919
#4

File đính kèm

Tham gia ngày
3 Tháng bảy 2009
Bài viết
2,373
Thích
2,244
Tuổi
19
#5
Em thấy phương thức getrows hoạt động không chuẩn lắm anh ah. Em có tạo 1 file kiểm tra, và thấy nó trả về kết quả không đúng. Anh có thể kiểm tra file giúp em xem có gì sai không ạ. Em cảm ơn
ủa không đúng chỗ nào vậy bạn ? và đúng thì phải là bao nhiêu ?
 
Tham gia ngày
11 Tháng tám 2015
Bài viết
528
Thích
258
#7
Bạn cho mình hỏi, File gộp nhiều File sau khi gộp thì tính ra tổng luôn được không bạn. Ví dụ file ca1 có số lượng 1,2,3; file ca 2 có số lượng 4,5,6. File gộp sẽ là 21.
 

queluatb

Thành viên hoạt động
Tham gia ngày
17 Tháng một 2011
Bài viết
197
Thích
15
#9
file rất hay, cám ơn chủ top nhiều, e cũng mong chủ top giúp đỡ thêm, e muốn đưa file này vào làm file tính tổng hợp lương, file này của chủ tốp có thể lấy tiền lương của mọi người, nhưng không lấy được số người, vì bảng lương người lao động có tháng thì có người này, có tháng thì không có người này, có tháng tăng có tháng giảm người mong chủ top lưu ý giúp và giúp e giải pháp, chân thành cám ơn
 

khieukienag

Thành viên thường trực
Tham gia ngày
29 Tháng tư 2008
Bài viết
203
Thích
116
#10
Tổng hợp số liệu từ nhiều file

Xin code để tổng hợp số liệu từ 5 file vào 1 file. Cả 6 file nầy đều có cấu trúc giống nhau:
SoLieu.gif
Xin cảm ơn
 

File đính kèm

Lần chỉnh sửa cuối:

ndu96081631

Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
29,180
Thích
50,562
#11

toantien1989

Thành viên mới
Tham gia ngày
16 Tháng ba 2011
Bài viết
1
Thích
0
#12
Thầy ndu96081631 ơi cho con FILE TONG HOP được lưu theo định dạng XLSM khác tổng hợp theo cách lũy kế chèn ở dưới giùm mình được không ? ( của thầy là lũy kế chèn ở trên ) cám ơn thầy rất nhiều !
 

File đính kèm

Lần chỉnh sửa cuối:

khieukienag

Thành viên thường trực
Tham gia ngày
29 Tháng tư 2008
Bài viết
203
Thích
116
#13
Cảm ơn sự quan tâm của ndu96081631.
Xin kèm file nén có 6 file. Trong file TongHop có ghi yêu cầu cộng số liệu từ 5 file kia.
Xin cảm ơn.
 

File đính kèm

be09

Thành viên gạo cội
Tham gia ngày
9 Tháng tư 2011
Bài viết
5,574
Thích
5,511
Tuổi
60
#14
Cảm ơn sự quan tâm của ndu96081631.
Xin kèm file nén có 6 file. Trong file TongHop có ghi yêu cầu cộng số liệu từ 5 file kia.
Xin cảm ơn.
Nếu File đơn giản quá thì làm vầy (lẹ hơn hỏi code).

Click vào B4 sheet1 File tổng hợp, gõ dấu nháy trước dấu bằng (=).

Tiếp theo Copy dấu $, xong nhấn Ctrl+H hộp thoại Find and Replace hiện ra, trong khung Find what dán dấu $ vào rồi nhấn Replace (xong).

Bây giờ vào B4 Fill ngang và Fill xuống (xong).
 

ndu96081631

Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
29,180
Thích
50,562
#16

khieukienag

Thành viên thường trực
Tham gia ngày
29 Tháng tư 2008
Bài viết
203
Thích
116
#17
Kính gửi các bác hoanganhdl, ndu96081631, be09,
Nhân bài viết Tổng hợp số liệu nhiều file (chép dữ liệu) không cần mở file mang dạng tổng quát rất hay của bác ndu96081631, chúng tôi muốn nhờ giúp cộng số liệu của nhiều file không cần mở file vào file TongHop. Yêu cầu được ghi trong file TongHop kèm theo. Xin cảm ơn. EXCEL.gif
 

File đính kèm

kuldokk

Thành viên hoạt động
Tham gia ngày
12 Tháng mười 2007
Bài viết
147
Thích
156
#18
Nếu ai muốn biết dữ liệu nào đến từ file nào thì có thể thay đổi câu SQL thành dạng như sau:

"SELECT *,""" & files(k) & """ as [From File] FROM [" & SheetName & RangeAddress & "]"

câu này sẽ thêm 1 cột "From File" để các bạn biết được dữ liệu từ file nào đến.
 

traicauhd

Thành viên mới
Tham gia ngày
7 Tháng tư 2011
Bài viết
43
Thích
0
Tuổi
28
#19
Chào bạn mình rất thích code của bạn mình muốn sửa code thành coppy cột a đến cột e của 1 sheep rồi thực hiện thêm 1 code của mình nữa rồi lại tiếp tuc coppy dữ liệu file excel thuws 2 Cứ như vậy cho đến hết các file excel


Vậy ai giúp mình được ko
 
Lần chỉnh sửa cuối:
Top