Tổng hợp dữ liệu không cần mở File (2 người xem)

[huy vu]

Em có câu hỏi sau, mong các thành viên giúp đỡ.
Em có file Tổng hợp, và nhiều file Số tiền các tháng T1, T2, T3....
Cấu trúc các file:
- File Tong Hop gồm trường: Code, Sotien1, VAT1, Sotien2.....
- File số tiền gồm 3 trường: Code, SoTien, VAT
Bình thường e hay copy dữ liệu file T1, T2... vào các sheet của file Tổng hợp
Rồi từ đó dùng phương thức Find để tìm kiếm theo Code của sheet Tổng hợp từ các sheet T1, T2...
Bài toán đặt ra là dữ liệu T1, T2 càng ngày càng nhiều, copy vào file Tổng hợp làm nặng file.
Vậy có cách nào mà Tổng hợp dữ liệu không cần copy các file T1, T2 vào các sheet của file tổng hợp không?
Tổng hợp dữ liệu không cần mở file. Hình như ADO có thể làm được việc này, hoặc bằng phương thức khác, mong các thành viên giúp đỡ!!!
Em xin chân thành cảm ơn!

 

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

Em cảm ơn a, vì cơ bản còn yếu nên không biết sửa ở đâu ạ.
Em đang muốn tổng hợp vào sheet TongHop thì code thế nào ạ.

Thì tôi đã sửa cho bạn ở bài #39 rồi mà bạn.

 

[huy vu]

Thì tôi đã sửa cho bạn ở bài #39 rồi mà bạn.

Sheet Data các mã code là cố định, em muốn tổng hợp theo các code này anh ạ. kết quả Sumif như bài #38 ý anh.
Em cảm ơn.

 

[Hau151978]

Dữ liệu trong sheet DATA cần sửa lại kiểu number cho giống các file T1, T2, T3.
Thử với file T1 trước nhé, code để lấy tất cả các file tương tự. Mình sử dụng cách thủ công:
- Đặt Name Code =DATA!$D$3:$D$62
- Tạo thêm sheet5 lưu dữ liệu từ file T1 rồi join với bảng code (bỏ qua phần bẫy lỗi do lười gõ phím, các sư phụ thông cảm!)

Function ConnXLS(ByVal Fname As String) As ADODB.Connection
    Dim cnn As New ADODB.Connection, str$
    str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                 Fname & _
                ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
    cnn.Open str
    Set ConnXLS = cnn
End Function


Sub test()
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset, str$
    str = ThisWorkbook.Path & "\T1.xlsb"
    Set cnn = ConnXLS(str)
    Set rs = New ADODB.Recordset
    str = "SELECT * FROM [T1$]"
    rs.Open str, cnn
    Sheet5.Range("A2:C1000").ClearContents
    Sheet5.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
    Set cnn = ConnXLS(ThisWorkbook.FullName)
    str = "SELECT [Sheet5$].SOTIEN,[Sheet5$].VAT FROM code LEFT JOIN [Sheet5$] ON [sheet5$].code=code.code"
    rs.Open str, cnn
    Sheets("DATA").Range("O4").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

Ở đây mình giả thiết code trong T1 duy nhất, nếu code không duy nhất cần tính tổng tiền thì sửa lại lệnh SELECT thứ nhất thành "SELECT code, SUM(SoTien) AS SoTien, SUM(VAT) AS VAT FROM [T1$] GROUP BY code"

 

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

Dữ liệu trong sheet DATA cần sửa lại kiểu number cho giống các file T1, T2, T3.
Thử với file T1 trước nhé, code để lấy tất cả các file tương tự. Mình sử dụng cách thủ công:
- Đặt Name Code =DATA!$D$3:$D$62
- Tạo thêm sheet5 lưu dữ liệu từ file T1 rồi join với bảng code (bỏ qua phần bẫy lỗi do lười gõ phím, các sư phụ thông cảm!)

Function ConnXLS(ByVal Fname As String) As ADODB.Connection
    Dim cnn As New ADODB.Connection, str$
    str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                 Fname & _
                ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
    cnn.Open str
    Set ConnXLS = cnn
End Function


Sub test()
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset, str$
    str = ThisWorkbook.Path & "\T1.xlsb"
    Set cnn = ConnXLS(str)
    Set rs = New ADODB.Recordset
    str = "SELECT * FROM [T1$]"
    rs.Open str, cnn
    Sheet5.Range("A2:C1000").ClearContents
    Sheet5.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
    Set cnn = ConnXLS(ThisWorkbook.FullName)
    str = "SELECT [Sheet5$].SOTIEN,[Sheet5$].VAT FROM code LEFT JOIN [Sheet5$] ON [sheet5$].code=code.code"
    rs.Open str, cnn
    Sheets("DATA").Range("O4").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

Ở đây mình giả thiết code trong T1 duy nhất, nếu code không duy nhất cần tính tổng tiền thì sửa lại lệnh SELECT thứ nhất thành "SELECT code, SUM(SoTien) AS SoTien, SUM(VAT) AS VAT FROM [T1$] GROUP BY code"

Câu sau so với dữ liệu thực tế thì hơi bị lạ

str = "SELECT [Sheet5$].SOTIEN,[Sheet5$].VAT FROM code LEFT JOIN [Sheet5$] ON [sheet5$].code=code.code"

Hình như cũng chưa cần thiết phải thêm sheet phụ là sheet5 như thế

 

[VetMini]

...
Hình như cũng chưa cần thiết phải thêm sheet phụ là sheet5 như thế

Nếu là tôi, thì tôi cũng không lập sheet phụ. Vì tôi hết sức tránh việc lập connection với file chính. Trong ứng dụng VBA, ADO có cái bug là lúc connection close rồi nó vẫn nó không nhả ra phần bộ nhớ đã sử dụng để kết nối với file đang mở. Vì thế nếu nối nhiều lần sẽ bị tràn bộ nhớ.

GROUP BY: nếu là tôi, tôi cũng không group trước khi join, luôn luôn group sau khi join, trừ phi không thể khác hơn.

 

[Hau151978]

Bảng code ở sheet DATA cố định rồi, trong sheet5 có những dữ liệu thừa thì loại ra. Ví dụ: các file T1, T2, T3 tổng hợp nhiều cơ sở báo về nhưng tác giả chỉ phụ trách 1 số cơ sở chứ không phải tất cả nên cần LEFT JOIN.
Lý do lập sheet phụ vì tôi chưa biết cách JOIN 2 bảng ở 2 file excel khác nhau.
Còn lý do không GROUP BY trước khi JOIN thì mình không biết, mong các bác giải thích? Có phải để tránh những phép tính thừa đối với các code không được JOIN không?

 

[Lê Phát Huy]

Bảng code ở sheet DATA cố định rồi, trong sheet5 có những dữ liệu thừa thì loại ra. Ví dụ: các file T1, T2, T3 tổng hợp nhiều cơ sở báo về nhưng tác giả chỉ phụ trách 1 số cơ sở chứ không phải tất cả nên cần LEFT JOIN.
Lý do lập sheet phụ vì tôi chưa biết cách JOIN 2 bảng ở 2 file excel khác nhau.
Còn lý do không GROUP BY trước khi JOIN thì mình không biết, mong các bác giải thích? Có phải để tránh những phép tính thừa đối với các code không được JOIN không?

Tôi nghĩ việc left join với file đóng thì cũng đâu phải là khó đúng không bạn. Bạn cần suy luận thêm 1 chút nữa là được thôi mà.

 

[Hau151978]

Tôi nghĩ việc left join với file đóng thì cũng đâu phải là khó đúng không bạn. Bạn cần suy luận thêm 1 chút nữa là được thôi mà.

Vì câu lệnh tạo recordset "rs.Open str, cnn" mà cnn chỉ kết nối đến 1 file excel nên mình chưa biết cách join 2 bảng ở 2 file khác nhau vì vậy mình copy 2 bảng vào 1 file rồi mới truy vấn.

 

[Lê Phát Huy]

Vì câu lệnh tạo recordset "rs.Open str, cnn" mà cnn chỉ kết nối đến 1 file excel nên mình chưa biết cách join 2 bảng ở 2 file khác nhau vì vậy mình copy 2 bảng vào 1 file rồi mới truy vấn.

Chưa hẳn để join từ 1 bảng đến 1 bảng thì bạn phải mở đồng thời song song 2 CSDL nguồn để join. Bạn có thể mở 1 kết nối và sau đó join đến file đang đóng là được.

 

[VetMini]

Group sau khi join:
1. Câu lệnh SQL dễ debug và dễ khai triển hơn.
2. Nếu phải nối nhiều bảng thì các con toán tính tổng kết (aggregate functions) để sau cùng sẽ tránh bớt được tình trạng sai số do mất số lẻ.

Trong trường hợp bài này trước và sau không khác nhau gì cả. Nhưng tập thói quen thì vẫn hơn.

 

[Lê Phát Huy]

Group sau khi join:
1. Câu lệnh SQL dễ debug và dễ khai triển hơn.
2. Nếu phải nối nhiều bảng thì các con toán tính tổng kết (aggregate functions) để sau cùng sẽ tránh bớt được tình trạng sai số do mất số lẻ.

Trong trường hợp bài này trước và sau không khác nhau gì cả. Nhưng tập thói quen thì vẫn hơn.

Xin nói thêm:
Đối với lập trình chuyên nghiệp thì ta không nên group trước vì điều đơn giản là sau đó ta cũng phải mất công group lại nữa. Với CSDL nhỏ thì ta không thấy sự khác biệt cho lắm, nhưng với CSDL lớn thì thời gian để làm điều đó sẽ có sự khác biệt rất rõ.

 

[Hau151978]

Chưa hẳn để join từ 1 bảng đến 1 bảng thì bạn phải mở đồng thời song song 2 CSDL nguồn để join. Bạn có thể mở 1 kết nối và sau đó join đến file đang đóng là được.

Vấn đề này mình chưa biết, bạn có thể cho 1 đoạn code được không? Nói chung là truy vấn trên 2 table ở 2 file excel khác nhau.

 

[Lê Phát Huy]

Vấn đề này mình chưa biết, bạn có thể cho 1 đoạn code được không? Nói chung là truy vấn trên 2 table ở 2 file excel khác nhau.

Google 1 phát có nhiều, bạn nên tự tìm hiểu sẽ nhớ dai hơn nhé.

 

[huy vu]

Mong anh Vetmini và Lê Phát Huy có giải pháp tối ưu hơn, để em và nhiều người trên gpe học tập.

 

[Lê Phát Huy]

Mong anh Vetmini và Lê Phát Huy có giải pháp tối ưu hơn, để em và nhiều người trên gpe học tập.

Vậy kết quả bạn cần là gì?

 

[huy vu]

Vậy kết quả bạn cần là gì?

Kết quả như bài #38
ở sheet data, đang phải dùng 3 hàm sumif tham chiếu về 3 sheet, giờ ko cần copy các dữ liệu vào các T1, T2.. mà lấy ra kết quả trực tiếp luôn.

 

[VetMini]

Mong anh Vetmini và Lê Phát Huy có giải pháp tối ưu hơn, để em và nhiều người trên gpe học tập.

Quan niệm về "tối ưu" của tôi khác với nhiều người trên diễn đàn này. Mỗi lần nhìn thấy từ này thì tôi dị ứng.

Những cái bạn vừa hỏi đều có thể tìm được bên hộp CSDL, thớt Đố Vui về ADO...
Bạn HLMT đã có dẫn qua, nhưng hìn như bạn không chịu đọc.

 

[Hau151978]

Mình sửa lại bài 38 code để tính tổng và không dùng sheet tạm, vẫn phải SUM rồi mới JOIN vì nếu JOIN trước rồi group thì thứ tự code sẽ thay đổi.

Option Explicit


Function ConnXLS(ByVal Fname As String) As ADODB.Connection
    Dim cnn As New ADODB.Connection, str$
    str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                 Fname & _
                ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
    cnn.Open str
    Set ConnXLS = cnn
End Function


Sub Test()
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset, str$
    str = ThisWorkbook.FullName
    Set cnn = ConnXLS(str)
    Set rs = New ADODB.Recordset
    str = "SELECT T1.SoTien,T1.VAT FROM Code LEFT JOIN (SELECT Code,SUM(SoTien) AS SoTien,SUM(VAT) AS VAT FROM [Excel 12.0;HDR=Yes;IMEX=1;DATABASE=" & ThisWorkbook.Path & "\T1.xlsb].[T1$] GROUP BY Code) T1 ON Code.Code=T1.Code"
    rs.Open str, cnn
    Sheet4.Range("O4").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

 

[Lê Phát Huy]

Như bạn VetMini đã đề cập, trường hợp này cũng không hiếm ở diễn đàn này. Bạn nên tìm giải pháp khác thay vì dùng sheet tạm.

 

[thanhnt1910]

Nếu dữ liệu của em từ 2 file excel thuộc 2 thư mục khác nhau thì làm thế nào ạ.