Xin giúp đỡ về câu lệnh truy vấn sử dụng ADO đưa dữ liệu từ vào Excel

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

Test kỹ lại File của anh, nếu report có cấu trúc khác data (cách dòng) thì dữ liệu cũng không hiển thị.

Hiển thị chứ bạn, điều này tôi đảm bảo với bạn, nếu như mã bên sheet Data và mã bên sheet Report có tương ứng.
File của bạn không ra là đúng vì sheet Report bạn ghi mã 0050, 0030 mã này không có tồn tại bên sheet Data nên nó không ra là đúng.
Ta nên loại bỏ dòng trống thừa khi lấy kq nhé bạn.

 

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

Code của a ThuNghi chỉ cần thêm bẫy lỗi khéo một chút là đáp ứng được yêu cầu.
Tuy hơi phức tạp nhưng gọn gàng hơn nếu dùng ADO phải đưa dữ liệu ra, vô.

Cám ơn các anh nhiều

Nếu không muốn đưa vào, đưa ra thì bạn có thể đưa vào 1 phát = Update Query. Vừa nhanh, vừa gọn, lại vừa dể hiểu.

 

[ThuNghi]

Hiển thị chứ bạn, điều này tôi đảm bảo với bạn, nếu như mã bên sheet Data và mã bên sheet Report có tương ứng.
File của bạn không ra là đúng vì sheet Report bạn ghi mã 0050, 0030 mã này không có tồn tại bên sheet Data nên nó không ra là đúng.
Ta nên loại bỏ dòng trống thừa khi lấy kq nhé bạn.

Theo tôi thì nếu sử dụng ADO thì nên dùng để lấy Data thôi, lấy xong rồi mà muốn Report trên Ex thì xử lý trên Ex cho khỏe.
Chớ ai lại dùng ADO chuyển report sang Acc rồi từ Data kết hợp Join để tạo ra 1 report và lấy chuyển qua Ex. Phức tạp quá.
Còn vấn đề code của mình nếu bẫy lỗi khi report kg có mã thì dễ mà. Code chỉ phức tạp ở chuyển từ ngang qua dọc, còn kg chả có gì, kg cần dùng Dic cũng OK. Thêm vòng lặp theo hướng xử lý Arr cũng nhanh.

If nR then...
end if

Đàng nào cũng phải.
1/ Dùng Ado lấy Data -> rec
2/ Convert Rec sang Arr
3/ Convert Arr ngang sang dọc (transpose) kết hợp luôn lấy số dòng hiển thị mã (nR=Dic.Item)
5/ Duyệt lại Report, nếu tìm ra nR thì gán cột còn lại, còn kg thì thôi.
Code dùng arr của mình báo lỗi do
1/ Data có A06 là trùng
Vậy phải thêm

If Not Dic.Exists(sMa) Then
    Dic.Add sMa, iR + 1
  End If

2/ Những mã kg có trong Data thì thêm

If nR Then
      For iC = 1 To 2
        ArrKQ(iR, iC) = sArr(nR, iC + 1)
      Next iC
    Else
      ArrKQ(iR, 1) = "Nothing"
    End If

 

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

Theo tôi thì nếu sử dụng ADO thì nên dùng để lấy Data thôi, lấy xong rồi mà muốn Report trên Ex thì xử lý trên Ex cho khỏe.
Chớ ai lại dùng ADO chuyển report sang Acc rồi từ Data kết hợp Join để tạo ra 1 report và lấy chuyển qua Ex. Phức tạp quá.
Còn vấn đề code của mình nếu bẫy lỗi khi report kg có mã thì dễ mà. Code chỉ phức tạp ở chuyển từ ngang qua dọc, còn kg chả có gì, kg cần dùng Dic cũng OK. Thêm vòng lặp theo hướng xử lý Arr cũng nhanh.

If nR then...
end if

Đàng nào cũng phải.
1/ Dùng Ado lấy Data -> rec
2/ Convert Rec sang Arr
3/ Convert Arr ngang sang dọc (transpose) kết hợp luôn lấy số dòng hiển thị mã (nR=Dic.Item)
5/ Duyệt lại Report, nếu tìm ra nR thì gán cột còn lại, còn kg thì thôi.

Em làm cái Update Query luôn, anh xem coi nó rất đơn giản.
Cái này em kết nối với CSDl Access, rồi update trực tiếp vào sheet Report, không qua 1 bảng trung gian nào.

Sub HLMT_ADO()
Dim Cn As New ADODB.Connection
With Cn
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\DB.mdb"


        sSQL = "UPDATE [Data] a " _
                & "INNER JOIN " _
                & "[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & ThisWorkbook.FullName & "].[Report$] b  " _
                & "ON a.ma=b.ma " _
                & "SET b.f01=a.f01, b.f02=a.f02"
        [B2:C100].ClearContents
    .Execute sSQL
    .Close
End With
Set Cn = Nothing


End Sub

 

[ThuNghi]

Em làm cái Update Query luôn, anh xem coi nó rất đơn giản.
Cái này em kết nối với CSDl Access, rồi update trực tiếp vào sheet Report, không qua 1 bảng trung gian nào.

Sub HLMT_ADO()
Dim Cn As New ADODB.Connection
With Cn
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\DB.mdb"


        sSQL = "UPDATE [Data] a " _
                & "INNER JOIN " _
                & "[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & ThisWorkbook.FullName & "].[Report$] b  " _
                & "ON a.ma=b.ma " _
                & "SET b.f01=a.f01, b.f02=a.f02"
        [B2:C100].ClearContents
    .Execute sSQL
    .Close
End With
Set Cn = Nothing


End Sub

Quá hay ADO kết hợp cả tbl acc và sh của Ex, đang học hỏi nhưng hơi khó.
HLMT cho hỏi thử câu nào là gán vào sh Report vậy

.Execute sSQL

Và nếu gán vào dòng 100 thay ví A 1 thì sửa thế nào
Cám ơn.

 

[KVP]

Quả thật là E cũng không ngờ và chưa thâý sự kết hợp này bao giờ (cả Google luôn). Code gọn lỏn àh
Lúc trước cũng đã học hỏi anh được một chiêu trong ADO giờ lại được chiêu nữa.
Như vậy là E sử dụng 2 phương án cho tình huống này. ADO và Dic. cho nó chắc và ổn định.

 

[KVP]

Quá hay ADO kết hợp cả tbl acc và sh của Ex, đang học hỏi nhưng hơi khó.
HLMT cho hỏi thử câu nào là gán vào sh Report vậy

.Execute sSQL

Và nếu gán vào dòng 100 thay ví A 1 thì sửa thế nào
Cám ơn.

Theo Em, Code này không copy từ Rec mà update và Range name định sẵn của report. Nếu muốn gán vào dòng 100 thì chỉ có di chuyển cả name theo địa chỉ tương ứng hoặc Update song copy lại thôi à.

 

[Hoàng Trọng Nghĩa]

Sẳn đề tài này về ADO, cho tôi hỏi, giữa 2 thủ tục dưới đây, thủ tục nào cũng cho ra kết quả đúng và thời gian cũng như nhau, vậy chúng ta nên dùng cấu trúc nào để tối ưu nhất:

Thủ tục 1:

            sSQL = "SELECT Sum(NgayPhep) " _
                 & "FROM TB_LuongThucTe " _
                 & "WHERE KyLuong Like '%2012' AND MaTinhLuong = 'TM00001' " _
                 & "GROUP BY MaTinhLuong"

Thủ tục 2:

            sSQL = "SELECT Sum(NgayPhep) " _
                 & "FROM TB_LuongThucTe " _
                 & "WHERE KyLuong Like '%2012' " _
                 & "GROUP BY MaTinhLuong " _
                 & "HAVING MaTinhLuong = 'TM00001'"

 

[ptm0412]

Theo Em, Code này không copy từ Rec mà update và Range name định sẵn của report. Nếu muốn gán vào dòng 100 thì chỉ có di chuyển cả name theo địa chỉ tương ứng hoặc Update song copy lại thôi à.

Câu lệnh SQL là Update, đích là recordset trên sheet của Excel, vậy recordset Excel nằm đâu, Update vào đó.
Tuy nhiên, nếu "Report" là record set lấy từ sheet name, đương nhiên là bắt đầu từ A1
Nếu A100 hoặc chỗ khác, thì phải đặt Name và recordset lấy từ Range Name, thay vì sheet name

 

[thuyyeu99]

Câu lệnh SQL là Update, đích là recordset trên sheet của Excel, vậy recordset Excel nằm đâu, Update vào đó.
Tuy nhiên, nếu "Report" là record set lấy từ sheet name, đương nhiên là bắt đầu từ A1
Nếu A100 hoặc chỗ khác, thì phải đặt Name và recordset lấy từ Range Name, thay vì sheet name

Anh có thể cho em 1 ví dụ nho nhỏ được không anh ?

 

[KVP]

Bạn xem trong File đính kèm, xin phép mượn code của Anh HLMT
Ở đây mình đặt Name có tên RangeName

 

[ptm0412]

Chỉ có 1 câu giải thích nho nhỏ: http://www.giaiphapexcel.com/forum/...ase-cho-công-việc-kế-toán&p=365750#post365750

ADO có thể lấy dữ liệu từ các bảng (table), không phải chỉ lấy từ tên sheet.
Nếu 1 sheet chỉ chứa 1 bảng, ta có thể dùng tên sheet làm tên table, và ADO hiểu. Nếu thêm $ thì càng tốt thí dụ [Data$]
Nếu 1 sheet chứa nhiều table như sheet DM (3 bảng danh mục), ta sẽ đặt name cho từng vùng, name đó trở thành tên table

 

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

Sẳn đề tài này về ADO, cho tôi hỏi, giữa 2 thủ tục dưới đây, thủ tục nào cũng cho ra kết quả đúng và thời gian cũng như nhau, vậy chúng ta nên dùng cấu trúc nào để tối ưu nhất:

Thủ tục 1:

            sSQL = "SELECT Sum(NgayPhep) " _
                 & "FROM TB_LuongThucTe " _
                 & "WHERE KyLuong Like '%2012' AND MaTinhLuong = 'TM00001' " _
                 & "GROUP BY MaTinhLuong"

Thủ tục 2:

            sSQL = "SELECT Sum(NgayPhep) " _
                 & "FROM TB_LuongThucTe " _
                 & "WHERE KyLuong Like '%2012' " _
                 & "GROUP BY MaTinhLuong " _
                 & "HAVING MaTinhLuong = 'TM00001'"

Cả 2 cách này đều được. Tuy nhiên cái thủ tục 2 nếu dùng Having thì phải như sau:

sSQL = "SELECT Sum(NgayPhep) " _
                 & "FROM TB_LuongThucTe " _
                 & "GROUP BY MaTinhLuong " _
                 & "HAVING MaTinhLuong = 'TM00001' and KyLuong Like '%2012'"

Còn nếu dùng where thì theo cái thủ tục 1 của anh.

Xin nói thêm khi nào dùng where và khi nào dùng having trong Query gom nhóm: Theo kinh nghiệm non kém của em thì cú pháp như sau

1./ Where:

Select .......
          From ........
         [COLOR=#ff0000] Where[/COLOR] .......
          Group by......

2./ Having:

Select .......
          From ........
         [COLOR=#FF0000] [/COLOR]Group by......
          [COLOR=#ff0000]Having [/COLOR].......

Nói túm lại Having là điều kiện lọc phải đứng sau Group by, ngược lại Where là điều kiện lọc phải đứng trước Group by.

 

[ThuNghi]

Cả 2 cách này đều được. Tuy nhiên cái thủ tục 2 nếu dùng Having thì phải như sau:

Còn nếu dùng where thì theo cái thủ tục 1 của anh.

sSQL = "SELECT Sum(NgayPhep) " _
                 & "FROM TB_LuongThucTe " _
                 & "WHERE Sum(NgayPhep)> 2  and KyLuong Like '%2012' AND MaTinhLuong = 'TM00001' " _
                 & "GROUP BY MaTinhLuong"

Theo như mới google thì cú pháp having có ứng dụng riêng <> where.
Với code trên nếu muốn select sum(ngayphep) where sum(ngayphep) > 2 thì kg được mà phải dùng having.
mà phải dùng

sSQL = "SELECT Sum(NgayPhep) " _
                 & "FROM TB_LuongThucTe " _
                 & "WHERE KyLuong Like '%2012' AND MaTinhLuong = 'TM00001' " _
                 & "GROUP BY MaTinhLuong having Sum(NgayPhep)> 2"

Với select mà có điều kiện sum ... thì kg thể gán ở where mà phải having.
Do chưa có bài để test nên chỉ phác họa.
Mấy điều này học từ file kế toán của Bác Mỹ làm = ADO lấu rồi => nghiệm ra nên chả biết giải nghĩa thế nào.

 

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

Anh thử

Nói túm lại Having là điều kiện lọc phải đứng sau Group by, ngược lại Where là điều kiện lọc phải đứng trước Group by.

Em nghĩ sẽ được đó anh.

 

[ptm0412]

Với select mà có điều kiện sum ... thì kg thể gán ở where mà phải having.
Do chưa có bài để test nên chỉ phác họa.
Mấy điều này học từ file kế toán của Bác Mỹ làm = ADO lấu rồi => nghiệm ra nên chả biết giải nghĩa thế nào.

Về vấn đề này cadafi có giải thích 1 lần rất ngắn gọn mà rất hay, tiếc là không tìm lại được bài ấy:
Đại khái là:
câu lệnh where sẽ lọc và trích xuất trước khi tính toán
having lọc và trích xuất sau khi tính toán

(nhớ không chắc lắm nhen, để hỏi lại cadafi)

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Em làm cái Update Query luôn, anh xem coi nó rất đơn giản.
Cái này em kết nối với CSDl Access, rồi update trực tiếp vào sheet Report, không qua 1 bảng trung gian nào.

Sub HLMT_ADO()
Dim Cn As New ADODB.Connection
With Cn
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\DB.mdb"


        sSQL = "UPDATE [Data] a " _
                & "INNER JOIN " _
                & "[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & ThisWorkbook.FullName & "].[Report$] b  " _
                & "ON a.ma=b.ma " _
                & "SET b.f01=a.f01, b.f02=a.f02"
        [B2:C100].ClearContents
    .Execute sSQL
    .Close
End With
Set Cn = Nothing


End Sub

Với cấu trúc này ta có thể update dữ liệu ngược lại từ file Excel của sheet report sang file access có bảng là data một cách rất dể dàng.
Ta chỉ cần thay a thành b của dòng "SET b.f01=a.f01, b.f02=a.f02" là được.