tổng hợp nhiều file về 1 file tổng, xóa bỏ các hàng thỏa điều kiện cho trước

[LuuGiaPhúc]

Nhờ các anh chị giúp em đoạn code khi click vào thì nó sẽ hiện ra bảng hỏi, mình chỉ đường dẫn đến và click chọn nhiều file báo cáo (có file dạng .xls , có file dạng .xlsx, có file dạng xlsb), có file chỉ có 1 sheet, cũng có file có nhiều sheet. Nó sẽ tổng hợp tất cả các file trên về file tổng.
Tiêu chí :
1. Nó sẽ copy toàn bộ file báo cáo và paste value về file tổng hợp, paste xong 1 sheet thì xuống hàng rồi mới paste tiếp sheet khác.

2. Trước khi copy và paste thì xem trong cột A và B của các file báo cáo, nếu thấy có các chữ : ATD , BAN , CMD , ZPC thì bỏ nguyên hàng đó, không copy

3. Xem trong cột G, nếu thấy chữ dummy thì cũng bỏ nguyên hàng, không copy hàng đó.

Cảm ơn các anh chị

 

[Hoàng Tuấn 868]

Dài quá, đọc chưa hết điều kiện đã oải.

 

[Maika8008]

Nhờ các anh chị giúp em đoạn code khi click vào thì nó sẽ hiện ra bảng hỏi, mình chỉ đường dẫn đến và click chọn nhiều file báo cáo (có file dạng .xls , có file dạng .xlsx), có file chỉ có 1 sheet, cũng có file có nhiều sheet. Nó sẽ tổng hợp tất cả các file trên về 1 file tổng.

Lưu ý : khi paste xong 1 sheet trong các file báo cáo về file tổng thì xuống 1 dòng rồi mới tiếp tục sheet thứ 2 .. đến hết thì qua tiếp file thứ 2 ..... (để sau này mình dễ kiểm tra lại xem nó đã paste được bao nhiêu sheet)

Điều kiện phụ :

1. Nhìn trong cột A và B của các file báo cáo, nếu thấy có những VP sau : ATD ,BAN , CMD , ZPC (có thể là ATD1 , ATD2 , BAN1 , BAN3..... nên mình thống nhất lấy 3 chữ cái đầu cho chắc ăn) tì xóa bỏ hàng đó, không cần dưa vào file tổng.

2. Nhìn trong cột G : thấy có chữ dummy thì cũng bỏ , không lấy (có thể là dummy_collection_agent , dummy_collection_1..... nên mình thấy chữ dummy là bỏ hết, không lấy hàng đó).
Cảm ơn các anh chị

View attachment 274498

Bạn phải gửi các file báo cáo kia nữa, chứ có 1 file thì làm thế nào?

 

[LuuGiaPhúc]

Bạn phải gửi các file báo cáo kia nữa, chứ có 1 file thì làm thế nào?

Dạ, cảm ơn anh,
em vừa sửa lại bài đăng, em up lên 2 file báo cáo và 1 file tổng hợp.

Cảm ơn anh

Bài đã được tự động gộp: 15/4/22

Dài quá, đọc chưa hết điều kiện đã oải.

hihi, cảm ơn bạn, mình đã sửa lại bài đăng cho ngắn gọn và dễ hiểu hơn, nhờ bạn giúp dùm nhé

 

[LuuGiaPhúc]

có anh chị nào giúp em không ạ,
em cảm ơn

 

[snow25]

Nhờ các anh chị giúp em đoạn code khi click vào thì nó sẽ hiện ra bảng hỏi, mình chỉ đường dẫn đến và click chọn nhiều file báo cáo (có file dạng .xls , có file dạng .xlsx, có file dạng xlsb), có file chỉ có 1 sheet, cũng có file có nhiều sheet. Nó sẽ tổng hợp tất cả các file trên về file tổng.
Tiêu chí :
1. Nó sẽ copy toàn bộ file báo cáo và paste value về file tổng hợp, paste xong 1 sheet thì xuống hàng rồi mới paste tiếp sheet khác.

2. Trước khi copy và paste thì xem trong cột A và B của các file báo cáo, nếu thấy có các chữ : ATD , BAN , CMD , ZPC thì bỏ nguyên hàng đó, không copy

3. Xem trong cột G, nếu thấy chữ dummy thì cũng bỏ nguyên hàng, không copy hàng đó.

Cảm ơn các anh chị

View attachment 274498

Anh @Hai Lúa Miền Tây viết code bài này dùng ado xem nó duyệt qua từng sheets thì code như thế nào anh nhỉ.

 

[Maika8008]

có anh chị nào giúp em không ạ,
em cảm ơn

Chạy thử file . . .

 

[LuuGiaPhúc]

Chạy thử file . . .

Tuyệt vời, mới test thử mấy trường hợp thì thấy ổn rổi. Cảm ơn rất nhiều
Cái nút Tổng hợp anh làm đẹp quá, hiệu ứng 3D luôn , hihihihi

 

[Hoàng Tuấn 868]

Chạy thử file . . .

Anh bổ sung thêm trường hợp không chọn và thoát phần chọn file mà không báo lỗi được không anh.

 

[Maika8008]

Anh bổ sung thêm trường hợp không chọn và thoát phần chọn file mà không báo lỗi được không anh.

Tôi làm như sau, còn người khác không biết thế nào:
1. Thêm mấy câu sau trong thủ tục GetMultipleFiles(), trước End With:

        On Error Resume Next
        ir = UBound(arr)
        If Err.Number = 9 Then Exit Sub

2. Thêm 2 câu sau trong thủ tục chính (ChepDL), sau dòng GetMultipleFiles

If Err.Number > 0 Then Exit Sub
On Error GoTo 0

@LuuGiaPhúc : Bạn thêm các dòng lệnh như tôi nói ở trên vào file nhé!

 

[Hoàng Tuấn 868]

Tôi làm như sau, còn người khác không biết thế nào:
1. Thêm mấy câu sau trong thủ tục GetMultipleFiles(), trước End With:

        On Error Resume Next
        ir = UBound(arr)
        If Err.Number = 9 Then Exit Sub

2. Thêm 2 câu sau trong thủ tục chính (ChepDL), sau dòng GetMultipleFiles

If Err.Number > 0 Then Exit Sub
On Error GoTo 0

@LuuGiaPhúc : Bạn thêm các dòng lệnh như tôi nói ở trên vào file nhé!

Em thử chạy tốt rồi, xin cảm ơn anh nhiều.

 

[VetMini]

Anh @Hai Lúa Miền Tây viết code bài này dùng ado xem nó duyệt qua từng sheets thì code như thế nào anh nhỉ.

"từng" là số ít (every/each/one after another), "mọi" () mới là số nhiều.

Có hai cách:
1. dùng Union All đọc tất cả các sheets cùng môt lúc. Lưu ý phải có "ALL", nếu không thì recordset sẽ bị lọc thành duy nhất (remove duplicates).
2. đọc từng sheet, tìm số records, ghi xuống và nhảy bao nhiêu đấy dòng trước khi qua sheet khác.
Tuy nhiên, nếu tôi hiểu dúng thì thớt muốn có một dòng trống ở giữa dữ liệu cóp từ các sheets. Nếu vậy thì cách 1 hơi khó - cũng làm được nhưng câu SQL hơi dài.

Phần qualifiers của câu SQL là:
WHERE Left(F1, 3) NOT IN ('ATD', 'BAN', 'CMD', 'ZPC') AND Left(F2, 3) NOT IN ('ATD', 'BAN', 'CMD', 'ZPC') AND F7 NOT LIKE '*dummy*'

 

[snow25]

"từng" là số ít (every/each/one after another), "mọi" () mới là số nhiều.

Có hai cách:
1. dùng Union All đọc tất cả các sheets cùng môt lúc. Lưu ý phải có "ALL", nếu không thì recordset sẽ bị lọc thành duy nhất (remove duplicates).
2. đọc từng sheet, tìm số records, ghi xuống và nhảy bao nhiêu đấy dòng trước khi qua sheet khác.
Tuy nhiên, nếu tôi hiểu dúng thì thớt muốn có một dòng trống ở giữa dữ liệu cóp từ các sheets. Nếu vậy thì cách 1 hơi khó - cũng làm được nhưng câu SQL hơi dài.

Phần qualifiers của câu SQL là:
WHERE Left(F1, 3) NOT IN ('ATD', 'BAN', 'CMD', 'ZPC') AND Left(F2, 3) NOT IN ('ATD', 'BAN', 'CMD', 'ZPC') AND F7 NOT LIKE '*dummy*'

Câu lệnh để duyệt qua từng sheets là gì anh nhỉ.

 

[Ngày mai trời lại sáng]

Câu lệnh để duyệt qua từng sheets là gì anh nhỉ.

Bác tham khảo thêm chủ đề này xem có giúp gì được không:

https://stackoverflow.com/questions/32396339/get-data-from-multiple-sheets-in-a-selected-workbook

 

[ongke0711]

Câu lệnh để duyệt qua từng sheets là gì anh nhỉ.

Nếu dùng ADODB, thì tôi dùng hàm sau để lấy tên Sheet đưa vào mảng rồi duyệt từng phần tử để nối câu lệnh SQL với Union All như anh Vietmini đã đề cập.

Function GetSheetsNames() As Variant
'Microsoft ActiveX Data Objects X.X Library
'Microsoft ADO Ext. X.X for DLL and Security

    Dim objCat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim sSheet As String

    Set objCat = New ADOX.Catalog
    Set objCat.ActiveConnection = oConn
    For Each tbl In objCat.Tables
        If Right(tbl.Name, 1) = "$" Or Right(tbl.Name, 2) = "$'" Then   'Có '$' là tên sheet
            sSheet = sSheet & Chr(10) & Replace(Replace(tbl.Name, "$", ""), "'", "")
        End If
    Next tbl
    GetSheetsNames = Split(Mid(sSheet, 2, Len(sSheet)), Chr(10))

    Set objCat = Nothing

End Function

 

[Ngày mai trời lại sáng]

Nếu dùng ADODB, thì tôi dùng hàm sau để lấy tên Sheet đưa vào mảng rồi duyệt từng phần tử để nối câu lệnh SQL với Union All như anh Vietmini đã đề cập.

Function GetSheetsNames() As Variant
'Microsoft ActiveX Data Objects X.X Library
'Microsoft ADO Ext. X.X for DLL and Security

    Dim objCat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim sSheet As String

    Set objCat = New ADOX.Catalog
    Set objCat.ActiveConnection = oConn
    For Each tbl In objCat.Tables
        If Right(tbl.Name, 1) = "$" Or Right(tbl.Name, 2) = "$'" Then   'Có '$' là tên sheet
            sSheet = sSheet & Chr(10) & Replace(Replace(tbl.Name, "$", ""), "'", "")
        End If
    Next tbl
    GetSheetsNames = Split(Mid(sSheet, 2, Len(sSheet)), Chr(10))

    Set objCat = Nothing

End Function

Wo,anh làm luôn với file ví dụ bài một cho em học hỏi với.

 

[Maika8008]

Wo,anh làm luôn với file ví dụ bài một cho em học hỏi với.

Bài này chạy mảng dễ hơn. Viết câu lệnh SQL cho được phát khùng luôn.

 

[LuuGiaPhúc]

Tôi làm như sau, còn người khác không biết thế nào:
1. Thêm mấy câu sau trong thủ tục GetMultipleFiles(), trước End With:

        On Error Resume Next
        ir = UBound(arr)
        If Err.Number = 9 Then Exit Sub

2. Thêm 2 câu sau trong thủ tục chính (ChepDL), sau dòng GetMultipleFiles

If Err.Number > 0 Then Exit Sub
On Error GoTo 0

@LuuGiaPhúc : Bạn thêm các dòng lệnh như tôi nói ở trên vào file nhé!

Dạ, em đã làm được rồi anh ơi, cảm ơn nhiều nha

 

[ongke0711]

Wo,anh làm luôn với file ví dụ bài một cho em học hỏi với.

Tôi dùng ADODB vì lấy dữ liệu đưa vào CSDL Access, chứ nếu cùng xử lý trên nền Excel thì dùng cách của bạn Maika8008 là nhanh gọn nhất rồi, tận dụng các phương thức, thuộc tính có sẵn của Excel đi đường vòng chi cho mệt. Tôi thì cứ ưu tiên chọn công cụ nào đơn giản, phù hợp hơn mà xài. Có những trường hợp truy vấn dữ liệu Excel chỉ cần dùng câu lệnh SQL đơn giản (hoặc Power Query) là có thể thay thế một đống code duyệt mảng, dictionary thì dùng SQL...

 

[VetMini]

Câu lệnh để duyệt qua từng sheets là gì anh nhỉ.

Tôi có thói xấu là "có mới nới cũ". Nhất là về kỹ thuật.
Đối với tôi, ADO là đồ xưa, nhiều đồ mới bi giờ hiệu quả hơn nhiều. Để thì giờ và công sức học chúng tốt hơn.

Mấy năm trước, diễn đàn này có một dạo nói nhiều về đồ ADO này. Tôi cũng đua đòi góp mấy bài trong đó. Bạn chịu khó tìm lại. Có cả kỹ thuật truy cập hạ tầng (metadata) qua DAO, ADOX, vv...

Chủ yếu bài #12 của tôi là nói về CSDL chung chung. Phần lọc trong câu SQL tôi viết ở trên là dùng được cho hầu hết các phiên bản của SQL (kể cả TSQL và Access). Chứ thực viết riêng cho Access thì phần IN (list of values) không hiệu quả bằng INSTR.