Đăng ký học Excel và VBA cùng GPE tháng 11 - TPHCM

Mua sách "VBA trong Excel - Cải thiện và tăng tốc" tái bản

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

Thảo luận trong 'Bài viết nổi bật và thông báo' bắt đầu bởi ndu96081631, 18 Tháng một 2017.

  1. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    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.

    [​IMG]


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

    Các file đính kèm:

    Chỉnh sửa lần cuối bởi điều hành viên: 28 Tháng tám 2017
  2. khieukienag

    khieukienag Thành viên thường trực

    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
     
  3. hoanganhdl

    hoanganhdl Thành viên hoạt động

    Đố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.
     
  4. quanluu1989

    quanluu1989 Thành viên gắn bó

    Các file đính kèm:

  5. doveandrose

    doveandrose hello

    ủa không đúng chỗ nào vậy bạn ? và đúng thì phải là bao nhiêu ?
     
  6. quanluu1989

    quanluu1989 Thành viên gắn bó

    Em nhầm ạ.+-+-+-++-+-+-++-+-+-+
     
  7. songhovl201

    songhovl201 Thành viên tiêu biểu

    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.
     
  8. quanghnn

    quanghnn Thành viên mới

    cảm ơn bạn chủ top nhiều nhé, tìm mãi giờ mới thấy bài tổng hợp
     
  9. queluatb

    queluatb Thành viên hoạt động

    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
     
  10. khieukienag

    khieukienag Thành viên thường trực

    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
     

    Các file đính kèm:

    Lần chỉnh sửa cuối: 22 Tháng một 2017
  11. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    Tổng hợp ra làm sao? Sao bạn không ghi mẫu vào file?
     
  12. toantien1989

    toantien1989 Thành viên mới

    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 !
     

    Các file đính kèm:

    Lần chỉnh sửa cuối: 24 Tháng một 2017
  13. khieukienag

    khieukienag Thành viên thường trực

    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.
     

    Các file đính kèm:

  14. be09

    be09 Thành viên gạo cội

    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).
     
  15. vonga.vouu

    vonga.vouu Thành viên mới

    Xin cảm ơn thầy ndu96081631 nhiều ạ. Bài viết quá hay luôn.
    P/s: Các bạn trên diễn đàn nên tìm bài viết của thầy
    ndu96081631 nhé! Rất hay và độc đáo.
     
  16. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    Trong file bạn đã làm cả rồi. Còn hỏi gì nữa
     
  17. khieukienag

    khieukienag Thành viên thường trực

    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
     

    Các file đính kèm:

  18. kuldokk

    kuldokk Thành viên hoạt động

    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.
     
  19. traicauhd

    traicauhd Thành viên mới

    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: 3 Tháng hai 2017
  20. congatrong82

    congatrong82 Thành viên chính thức

Chia sẻ trang này