Lấy dữ liệu từ nhiều file (mỗi file có 3 đến 4 sheet) đưa vào file tổng hợp

[Hai Lúa Miền Tây]

Bạn có thể sửa code 1 chút , kiểu cho nó hiện lên cái bảng hỏi file report ở đâu được không, vì mỗi ngảy mình tải file về, mình phải lưu lại với tên khác nhau (để sau này cần truy cứu lại). Code của bạn bắt buộc phải đặt đúng tên, nếu không nó báo lỗi vậy nè. Còn nữa , mỗi file report có thể có 2 sheet là page 1 và page 2 , cũng có file có 3 sheet hoặc 4 sheet , không cố định. Mình nhìn code của bạn hình như là khóa cứng 3 sheet luôn thì mình nghĩ nó không an toàn.

View attachment 263279

Dĩ nhiên code tôi viết trên chỉ là tham khảo. Cái tên file và tên sheet bạn phải tùy chỉnh cho phù hợp với tình hình của bạn.

 

[LuuGiaPhúc]

Như anh PTM đã hướng dẫn bạn xem lại cái file để chứa kết quả tổng hợp xem nó có bao nhiêu dòng. nếu là Office 2010 thì có hơn 1000000 dòng, các office khác hình như chỉ có 65536 dòng thôi. Trong code mình đã để mảng kết quả là Redim KQ( 1 to 1000000, 1 to 11) rồi. Code mình viết trên office 2010 bản 32bit thôi mà.

các file report Atteandance 29_07_2021.xls là xuất từ phần mềm xuống nên chắc chắn dưới 65536 dòng (nên nó mới có vụ page 2 , page 3 , page 4...) còn file tổng hợp là mình tạo mới, đuôi .xlsb hoặc xlsm. Hiện giờ mình copy bằng tay thì vẫn được. Code của bạn mình chạy lại thì giờ nó báo lỗi khác :

 

[HUONGHCKT]

các file report Atteandance 29_07_2021.xls là xuất từ phần mềm xuống nên chắc chắn dưới 65536 dòng (nên nó mới có vụ page 2 , page 3 , page 4...) còn file tổng hợp là mình tạo mới, đuôi .xlsb hoặc xlsm. Hiện giờ mình copy bằng tay thì vẫn được. Code của bạn mình chạy lại thì giờ nó báo lỗi khác :
View attachment 263299

Cái vụ tiếng tây này mình như người mù chữ thôi. bạn thử kiểm tra xem các Sh có Sh nào tên tiếng Việt có dấu không? hoạc kiểm tra xem trong dữ liệu ở các Sh có dòng nào trống (rỗng) không? Mình thấy lạ là file của bạn mình chạy code thấy êm du. mà up lại cho bạn, bạn chạy là báo lỗi.
Cách 1:Bạn thử mở code ra và chạy từng dòng code (bằng cách nhấn phím F8) xem nó bị ở dòng code nào.
Cách 2: bạn thử bỏ dòng code : On error go to Hand.... và chay code sau dó kiểm tra kết quả xem sao.
chúc thành công.

 

[befaint]

lấy dữ liệu từ nhiều file

Nhiều files là chỉ 2 files đã đính kèm hay nhiều hơn 2 files?

 

[LuuGiaPhúc]

Nhiều files là chỉ 2 files đã đính kèm hay nhiều hơn 2 files?

thường mỗi tháng chỉ có 2 đến 3 file , chưa khi nào nhiều hơn 3 file. Nhưng mỗi file có khi có 1 sheet, có khi có 2 , 3 , 4 sheet (tối đa cũng chỉ tới 4 sheet là hết). Các sheet này luôn đặt tên là page 1 , page 2 ... (do nó là report xuất từ hệ thống xuống nên không bao giờ có tên sheet khác chữ page 1 , page 2....).

 

[befaint]

thường mỗi tháng chỉ có 2 đến 3 file , chưa khi nào nhiều hơn 3 file

Vậy file thứ 3 như thế nào?
Cả 3 files có các sheets có cấu trúc giống y chang nhau hả?

 

[LuuGiaPhúc]

Vậy file thứ 3 như thế nào?
Cả 3 files có các sheets có cấu trúc giống y chang nhau hả?

Y chang nhau hết anh à, cách đặt tên sheet là page 1, page 2 ... số lượng cột , tiêu đề cột ... y chang nhau hết
Chú ý, dữ liệu của các sheet hay có tình trạng hàng cuối cùng bị thiếu (do các văn phòng nhập liệu trên phần mềm bị thiếu). nên mình cần chú ý nếu muốn dùng kiểu tìm hàng cuối cùng có dữ liệu để copy , em mấy lần đặt con trỏ chuột ở cột A, nhấn CTRL SHIFT , mũi tên xuống, tưởng đã chọn hết vùng, ai dè thiếu mấy hàng cuối

 

[befaint]

Ví dụ viết tạm một chương trình nhỏ nhỏ khoảng vài trăm $ thế này.

 

[LuuGiaPhúc]

Ví dụ viết tạm một chương trình nhỏ nhỏ khoảng vài trăm $ thế này.

ôi trời, anh đúng là cao thủ, nó chạy nhanh hết hồn luôn , hahaha.
Code này của anh nó vượt xa rất xa so với cái nhu cầu em cần, vô tình em lại nghĩ đến là áp dụng code này cho 1 tình huống khác mà bữa giờ em đang loay hoay làm thủ công, tình huống đó thì cực kỳ phù hợp với cái template này : sau khi import xong thì phải tổng kết xem source report nguồn toàn bộ có bao nhiêu sheet, mỗi sheet có bao nhiêu dòng..

Riêng trong cái nhu cầu của bài này, code này của anh có 1 điều hơi bất tiện , nếu có thể, anh giúp em chỉnh 1 chút xíu nhé.
Do em tải báo cáo về thì em cần phải lưu lại trong cùng 1 thư mục tên report , em chỉ đặt tên theo ngày tải về thôi , ví dụ
Atteandance 28_07_2021 - SBW.xls : là report tải ngày 28.07.2021 , ghi nhận lớp học SBW
Atteandance 28_07_2021- SD.xls : là report tải ngày 28.07.2021 , ghi nhận lớp học SD
Atteandance 29_07_2021 ESSS.xls : là report tải ngày 29.07.2021 , ghi nhận lớp học ESSS
................
nên nếu anh bắt buộc phải chỉ vào 1 thư mục thì lúc đó nó ôm hết toàn bộ report lại không được. thế là em phải tạo 1 thư mục mới, chép report qua đó, rồi chỉ đường dẫn cho nó, xong rồi phải cut mấy cái report về thư mục cũ của em, để cái thư mục mới nó trống cho lần sau import, chỉ là hơi bất tiện thôi, chứ mọi thứ code anh viết vẫn hoàn hảo.

Nên nếu có thể , anh cho nó hỏi theo file đi, em chọn file nào thì nó import những file đó thôi, không chơi kiểu "hoa thơm bứng cả cụm" thế này.

Em cảm ơn anh nhiều lắm lắm luôn á.

 

[ptm0412]

Sử dụng Power Query. Folder thêm file, file thêm sheet, chỉ cần refresh

let
   
    FolderName ="D:\MyPham\Downloads\Data",
    FileNameList= Folder.Files(FolderName)[Name],
    FileNum={0..List.Count(FileNameList)-1},
    DataF= List.Transform(FileNum, (f) =>
    let
        FFullName =FolderName & "\" & FileNameList{f},
        Source1 = Excel.Workbook(File.Contents(FFullName), null, true),
        SourceData=Source1[Data],
        SheetName=Source1[Name],
        SheetNum={0..List.Count(SheetName)-1},
        DataN= List.Transform(SheetNum, (i) =>
        let
            Data0 = Table.PromoteHeaders(Table.Skip(Source1[Data]{i},3)),
            RemoveBlank = Table.SelectRows(Data0, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
            DataFName = Table.AddColumn(Table.PromoteHeaders(RemoveBlank), "File", each FileNameList{f}),
            Datai = Table.AddColumn(DataFName, "Sheet", each SheetName{i})
        in Datai),
        List1 =  Table.FromList(DataN, Splitter.SplitByNothing()),
        ListColumns = Table.ColumnNames(List1{0}[Column1]),
        Ketqua1 = Table.ExpandTableColumn(List1,"Column1",ListColumns)
    in Ketqua1),
    List2 =Table.FromList(DataF,Splitter.SplitByNothing()),
    ListColumns2 = Table.ColumnNames(DataF{0}),
    Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2)
in
   Ketqua

Giải nén thư mục Data vào chỗ nào cũng được, lấy đường dẫn sửa đường dẫn thư mục ở dòng đầu tiên. Lưu ý như @Cá ngừ F1 có cảnh báo: trong 1 file có tiêu đề cột A có 2 ký tự xuống dòng phải sửa, 2 file đính kèm đã sửa
Muốn lấy bao nhiêu cột thì thêm 1 dòng lệnh xoá cột trong query editor.

 

[LuuGiaPhúc]

Sử dụng Power Query. Folder thêm file, file thêm sheet, chỉ cần refresh

let
 
    FolderName ="D:\MyPham\Downloads\Data",
    FileNameList= Folder.Files(FolderName)[Name],
    FileNum={0..List.Count(FileNameList)-1},
    DataF= List.Transform(FileNum, (f) =>
    let
        FFullName =FolderName & "\" & FileNameList{f},
        Source1 = Excel.Workbook(File.Contents(FFullName), null, true),
        SourceData=Source1[Data],
        SheetName=Source1[Name],
        SheetNum={0..List.Count(SheetName)-1},
        DataN= List.Transform(SheetNum, (i) =>
        let
            Data0 = Table.PromoteHeaders(Table.Skip(Source1[Data]{i},3)),
            RemoveBlank = Table.SelectRows(Data0, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
            DataFName = Table.AddColumn(Table.PromoteHeaders(RemoveBlank), "File", each FileNameList{f}),
            Datai = Table.AddColumn(DataFName, "Sheet", each SheetName{i})
        in Datai),
        List1 =  Table.FromList(DataN, Splitter.SplitByNothing()),
        ListColumns = Table.ColumnNames(List1{0}[Column1]),
        Ketqua1 = Table.ExpandTableColumn(List1,"Column1",ListColumns)
    in Ketqua1),
    List2 =Table.FromList(DataF,Splitter.SplitByNothing()),
    ListColumns2 = Table.ColumnNames(DataF{0}),
    Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2)
in
   Ketqua

Giải nén thư mục Data vào chỗ nào cũng được, lấy đường dẫn sửa đường dẫn thư mục ở dòng đầu tiên. Lưu ý như @Cá ngừ F1 có cảnh báo: trong 1 file có tiêu đề cột A có 2 ký tự xuống dòng phải sửa, 2 file đính kèm đã sửa
Muốn lấy bao nhiêu cột thì thêm 1 dòng lệnh xoá cột trong query editor.

Em cảm ơn anh đã hỗ trợ em.

 

[befaint]

chọn file nào thì nó import những file đó

Đây nhé .

 

[LuuGiaPhúc]

Tuyệt vời !! .
Xin nhận của em lời cảm ơn nhiều lắm :d

 

[alex-luu]

Đây nhé .

bạn ơi, code của bạn viết chạy đúng hết rồi, tuy nhiên mình lại không biết cách nào để đưa vào file của mình .
Mình cần đưa dữ liệu sau khi lấy dc vào Sheet 3, vùng từ D6:N. ,còn vùng S:X chứa công thức tính toán
File của bạn thì nó đang đưa kết quả vào sheet 1, vùng A1:K, bạn chỉnh lại giúp mình nhé. Cảm ơn nhiều ạ