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

Liên hệ QC

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


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.

32387587165_4ffaa22514_o.png[SIZE=3][SIZE=2]


Để 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

  • tong hop du lieu vao 1 file.rar
    51.2 KB · Đọc: 415
Chỉnh sửa lần cuối bởi điều hành viên:
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
 
Upvote 0
Đố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.
 
Upvote 0

File đính kèm

  • testgetrows.xlsm
    21.5 KB · Đọc: 43
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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

  • TongHopSoLieu.rar
    36.6 KB · Đọc: 96
Lần chỉnh sửa cuối:
Upvote 0
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

  • file-tong-hop.xls
    31 KB · Đọc: 31
Lần chỉnh sửa cuối:
Upvote 0
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

  • CongSoLieu.rar
    40.6 KB · Đọc: 67
Upvote 0
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).
 
Upvote 0
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

  • TongHopSoLieu.rar
    37.7 KB · Đọc: 51
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Web KT
Back
Top Bottom