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

ffcb1900 · 23/8/13

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).

Mong mọi người giúp đỡ với

be_09 · 20/2/14

ntktrungkien đã viết:
hiện tại em cũng có 2 file cần tổng hợp là 1 nhưng làm như hướng dẫn ko được. Nhờ các anh giúp với. Trong file excel của em có công thức cũng như 1 số đoạn code tổng hợp.
Em có tổng cộng 3 file: 2 file Team HN,HCM và 1 file tổng hợp.

Sử dụng nút gộp File, gộp sheet, tổng hợp, nội dung chi tiết xem sheet Hướng dẫn trong File

Lưu ý:

Tại B4 của sheet Hướng dẫn cần chọn loại Office 2003, 2007, 2010 của File cần gộp.

huunguyenfi · 22/2/14

ndu96081631 đã viết:
Dùng ADO sẽ không cần mở file:

Mã:

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 = "[B]Sheet1[/B]": RangeAddress = "A8:V10000" 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)

Chào Anh NDU96081631 , trước xin cám ơn anh về đoạn code trên . Nó giúp ích rất nhiều trong công việc của tôi . Nhưng trong quá trình sử dụng tôi còn gặp hai vướng mắc mong anh cũng như các pro khác giải đáp giúp .

- có thể nào cho excel tự hiểu sheet đầu tiên là sheet cần lấy dữ liệu cho dù sheet đầu tiên không phải mang tên là ''sheet1'' .
- trên sheet tổng hợp chỉ nhập những dữ liệu mới , lọc bỏ những dữ liệu trùng lập .

Mong được sự giải đáp của các pro . Thân .

ndu96081631 · 22/2/14

huunguyenfi đã viết:
- có thể nào cho excel tự hiểu sheet đầu tiên là sheet cần lấy dữ liệu cho dù sheet đầu tiên không phải mang tên là ''sheet1'' .
.

Code có tính đến trường hợp này mà bạn. Bạn cho SheetName = "" đồng nghĩa sẽ lấy Sheet đầu tiên

Mã:

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
    [COLOR=#ff0000]SheetName = ""[/COLOR]: RangeAddress = "A8:V10000"   ''<--- [COLOR=#ff0000]Gán SheetName = ""[/COLOR]
    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

Hoặc cũng không cần gán giá trị nào cho SheetName cũng được:

Mã:

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
    RangeAddress = "A8:V10000"  [COLOR=#ff0000]''<---- Xóa luôn dòng gán giá trị cho SheetName[/COLOR]
    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

------------------------------------

huunguyenfi đã viết:
- trên sheet tổng hợp chỉ nhập những dữ liệu mới , lọc bỏ những dữ liệu trùng lập .
.

Chưa hiểu ý bạn chỗ này! Như thế nào thì gọi là dữ liệu mới

huunguyenfi · 23/2/14

ndu96081631 đã viết:
Chưa hiểu ý bạn chỗ này! Như thế nào thì gọi là dữ liệu mới

Xin cám ơn Anh đã giải đáp khúc mắc . Về khúc mắc thứ 2 tức là : mỗi ngày tôi nhận được một file dữ liệu khoảng 500 dòng nhưng thực chất nó chỉ có khoảng 10 dòng là mới so với file dữ liệu tôi nhận được ngày hôm trước . Cho nên khi dùng code của anh để gộp 2 file này lại thi tôi được 1 file mới 1000 dòng trong khi yêu cầu của công việc tôi chỉ việc cập nhật thêm 10 dòng mới kia là đủ .
Hiện tại tôi dùng chức năng ''remove duplicates'' trong data để loại bỏ những dữ liệu trùng lập . Tôi tự hỏi liệu có thể tích hợp chức năng lọc dữ liệu sau khi gộp file này vào đoạn code của anh viết ở trên không . Thân .

ndu96081631 · 23/2/14

huunguyenfi đã viết:
Tôi tự hỏi liệu có thể tích hợp chức năng lọc dữ liệu sau khi gộp file này vào đoạn code của anh viết ở trên không . Thân .

Đương nhiên là làm được rồi (nhưng mà tôi bận quá)

huunguyenfi đã viết:
Hiện tại tôi dùng chức năng ''remove duplicates'' trong data để loại bỏ những dữ liệu trùng lập

Excel có chức năng gì phù hợp thì cứ xài đi, cũng đâu có tốn mấy công sức đâu
Ẹc... Ẹc...

anhdepjai · 28/2/14

Gửi thầy NDU, em muốn hỏi nếu mình tông hợp vài ô trong các sheet đóng VD: ô A10, ô C12 và ô D15 ... thành 1 dòng trong file tổng hợp thì làm thế nào ah? Em mò mẫm mãi mà không đi đượcThanks

huynhthanhphong93 · 9/3/14

Em có download file của thầy NDU về sử dụng chạy rất tốt. thầy có thể chỉ giúp em sửa code của thầy như thế nào để nếu trong file nguồn có 3 sheet đầu có dữ liệu thì khi chạy nó sẽ copy dữ liệu cả 3 sheet đó sang 3 sheet tương ứng bên file tổng hợp được không ạ. em cám ơn thầy nhiều ạ. :-=

tvl297 · 9/3/14

be09 đã viết:
Sử dụng nút gộp File, gộp sheet, tổng hợp, nội dung chi tiết xem sheet Hướng dẫn trong File

Lưu ý:

Tại B4 của sheet Hướng dẫn cần chọn loại Office 2003, 2007, 2010 của File cần gộp.

em thấy mỗi lần gộp các sheet lại có nhiều name rác, sao anh không viết luôn thêm đoạn code xoá name rác cho nó nhẹ

thichthibup · 11/3/14

Tổng hợp dữ liệu từ cáccột của nhiều file(cấu trúc bảng giống nhau) vào file tổng hợp

ndu96081631 đã viết:

Dùng ADO sẽ không cần mở file:

Mã:

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)[/QUOTE]
Vì thấy mong muốn của mình gần giống với Giải pháp của thầy [FONT=arial black][SIZE=2][COLOR=#0000CD]ndu96081631[/COLOR][/SIZE][/FONT] 
Nên tôi gởi bài vào đây: Rất mong mọi người giúp đỡ cho các file như mong muốn sau
1)Lấy tất cả các dữ liệu của nguyen van A1 ở cọt (số giờ dư/tháng 1) đưa vào cột D ở bảng tổng hợp
---
Tương cho cho những người khác
2)Lấy tất cả các dữ liệu của nguyen van A1 ở cọt (thành tiền của tháng 1) đưa vào cột E ở bảng tổng hợp
---
Tương cho cho những người khác; tương tự cho các tháng khác
Lưu ý: ở cột họ và tên(chỉ có từng đó con người) nhưng giữa các tháng là khác nhau về thứ tự
--------
Cảm ơn các bạn

KUMI · 14/3/14

Copy dữ liệu từ nhiều sheets của nhiều file vào nhiều sheets của 1 file.

Xin Chào Thầy Cô cùng các Anh Chị trên GPE!
Em cũng thấy chủ đề này tương tự với chủ đề em muốn hỏi nên em không tạo chủ đề mới nữa ạ.
Em có một vấn đề khó khắn dưới đây:

Em có rất nhiều file có định dạng "dd-mm-yyyy".xlsb
ví dụ: 01-01-2014.xlsb,02-01-2014.xlsb,03-01-2014.xls,....
Và trong mỗi file này có 3 Sheets em cần quan tâm đó là Lan1,Lan2,Lan3
Tất cả các file và các sheets đều đồng nhất với nhau về mặt cầu trúc dữ liệu.

Mong muốn là để làm sao đưa toàn bộ dữ liệu từ 3 sheets trong các file có định dạng "dd-mm-yyyy".xlsb vào 1 file TongHop.xlsb

Cụ thể là:
Trong cùng một thư mục có 16 file tương ứng với 16 ngày và trong mỗi file này lại có 3 sheets cần lấy dữ liệu (Sheets("Lan1"),Sheets("Lan2"),Sheest("Lan3")) và dữ liệu cần lấy từ vùng B6:AA222

Khi lấy vào sheets TongHop.xlsb thì file này sẽ có 16x3=48Sheet (sheet1,sheet2,....,sheet48)
Và trong mỗi sheets này sẽ hiển thì dữ liệu từ vùng B6:AA222

Ghi chú chỉ lấy dữ liệu theo kiểu xlPasteFormats,xlPasteValuesAndNumberFormats
Vì dữ liệu ở các file nguồn cũng có nhiều vùng chứa công thức.

Xin hỏi Thầy Cô và Anh Chị là mong muốn trên của em liệu có phương pháp nào giải quyết được không ạ?
Em rất mong nhận được sự giúp đỡ cụ thể cho trường hợp này!Em xin cảm ơn!

TrungChinhs · 14/3/14

KUMI đã viết:
Xin Chào Thầy Cô cùng các Anh Chị trên GPE!
Em cũng thấy chủ đề này tương tự với chủ đề em muốn hỏi nên em không tạo chủ đề mới nữa ạ.
Em có một vấn đề khó khắn dưới đây:

Xin hỏi Thầy Cô và Anh Chị là mong muốn trên của em liệu có phương pháp nào giải quyết được không ạ?
Em rất mong nhận được sự giúp đỡ cụ thể cho trường hợp này!Em xin cảm ơn!

Không tương tự đâu KUMI à: chủ đề này chỉ lấy dữ liệu, không lấy Format nên có thể dùng ADO mà không cần mở File nguồn. Còn yêu cầu của bạn thì lấy cả Format nên bắt buộc phải mở File nguồn.

Vấn đề bạn hỏi đương nhiên là làm được Bạn cứ gửi file lên sẽ có người giúp, còn tôi thì bó tay vì đang dùng Ex 2003.

KUMI · 14/3/14

TrungChinhs đã viết:
Không tương tự đâu KUMI à: chủ đề này chỉ lấy dữ liệu, không lấy Format nên có thể dùng ADO mà không cần mở File nguồn. Còn yêu cầu của bạn thì lấy cả Format nên bắt buộc phải mở File nguồn.

Vấn đề bạn hỏi đương nhiên là làm được Bạn cứ gửi file lên sẽ có người giúp, còn tôi thì bó tay vì đang dùng Ex 2003.

Cảm ơn Chú TrungChinhs đã góp ý ạ!
Mới đầu con cũng định sẽ gửi file nên nhưng nghĩ rằng với số lượng dữ liệu và file đính kèm như mong muốn đặt ra thì không thể gửi file nên đây được vì nặng, nên con đã cố gắng phân tích kỹ hơn vấn đề mong muốn đề mọi người hiểu được và có thể chỉ cần đưa code nên là đủ ạ.

Về file thì với trường hợp do những người dùng phiên bản office 2003 như Chú chẳng hạn nên con cũng đã chuyển về định dạng này mọi người cùng đóng góp.

Số lượng file kèm con đã giảm bớt từ 16 file xuống còn 5 file để giảm dung lượng và dễ test ,đồng thời trong file tổng hợp con cũng đã minh họa dữ liệu sau khi kết quả thực hiện xong.
Về việc lấy Format cũng không phải là cần thiết nên có thể bỏ cái này cũng được Chú ạ (nếu có thì sẽ tốt hơn) chỉ cần số liệu là được rồi ạ nếu cần format thì sau khi lấy kết quả xong con chỉ cần copyformat 1 sheet ở file nguồn rồi pase sang các sheets cũng được ạ.

Chú cùng các Thầy Cô xem file và tìm cách giúp con với nhé!

Ý!Do em không hiểu sâu vấn đề cứa tưởng mở file ngầm bằng code cũn thuộc dạng là không mở nên viết nhầm chủ đề như Chú TrungChinhs đã phân tích ở trên Phiền BQT giúp em tách chủ để này riêng ra với ạ.Em xin cảm ơn!(Em cũng định viết chủ đề mới nhưng để bài viết liền mạch với bài của Chú TrungChinhs nên mới tiếp tục ạ).

TrungChinhs · 14/3/14

Vẫn là File .xlsb bạn ạ

Bạn tạo 2 hoặc 3 file nguồn và 1 file tổng hợp dạng .xls. Để trong cùng một thư mục, nén lại rồi post lên.

KUMI · 14/3/14

Trời, Xin lỗi Chú vì con sơ xuất ạ!
Con gửi lại file kèm rồi Chú xem lại nhé!

vivatiamo · 2/4/14

ban cho mình hỏi làm sao để gộp nhiều file excel mà những file excel đó có chung 1 password. thanks nhiều ^^

hoanganhdl · 21/5/14

ndu96081631 · 22/5/14

hoanganhdl đã viết:
Thầy ndu xem giúp sao khi em chọn file này thì ko tổng hợp được dữ liệu vào nhưng nếu em Save as (ko chỉnh sửa gì) lại thì lại tổng hợp đươc? tks

Đây là file được lưu với định dạng "xa xưa": Microsoft Excel 4.0 ---> Bạn Save As nó thành Microsoft Excel 79-Excel 2003 là được

hoanganhdl · 22/5/14

ndu96081631 · 22/5/14

hoanganhdl đã viết:
Có khoảng 200 file con, có cách nào làm nhanh ko thầy?

Thử code này trên 1 file trắng:

Mã:

Sub ConvertToExl2003()
  Dim vFile, FileName
  On Error Resume Next
  vFile = Application.GetOpenFilename("Excel Files, *.xls", , , , True)
  If TypeName(vFile) = "Variant()" Then
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each FileName In vFile
      With Workbooks.Open(FileName)
        .SaveAs FileName, xlExcel9795
        .Close True
      End With
    Next
    MsgBox "Thành công"
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
  End If
End Sub

Chạy code, hộp mở file hiện ra, chọn file đầu, bấm phím Shift và chọn file cuối rồi bấm nút Open. Chờ đến khi thông báo "Thành công" hiện ra là xong
Để thí nghiệm, đừng chọn quá nhiều file nha. Chọn 1 vài file, sau khi code chạy xong hãy kiểm tra lại cho chắc chắn rồi hẳn tiếp tục

hoanganhdl · 22/5/14

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

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

File đính kèm

Biên Hòa, Đồng Nai

File đính kèm

Thành viên mới

Huyền thoại GPE

Thành viên mới

Huyền thoại GPE

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

Thành viên mới

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

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

File đính kèm

Bụi phấn

Thành viên tích cực

Bụi phấn

File đính kèm

Thành viên tích cực

Bụi phấn

Thành viên mới

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

Huyền thoại GPE

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

Huyền thoại GPE

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