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

[KVP]

Em có 2 Sheets. 1 Sheet làm Report, 1 sheet Data chứa dữ liệu (File đính kèm). Hiện giờ em muốn dùng ADO để đưa dữ liệu từ Data vào report.
Em thường dùng phương thức CopyFromRecordset để lấy dữ liệu, thông thường nếu cấu trúc Data và report giống nhau thì không có gì để nói. Nhưng khi report thay đổi cấu trúc một chút thì phương thức này không còn đúng nữa.
Nếu copy từng Recordset thì phải đóng, mở Recordset giải khiến Code dài và phức tạp. Hiện em đang loay hoay với vấn đề này mà chưa có cách giải quyết.

Rất mong anh chị xem xét, giúp em phương pháp tối ưu nhất.
Xin cám ơn

 

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

Em có 2 Sheets. 1 Sheet làm Report, 1 sheet Data chứa dữ liệu (File đính kèm). Hiện giờ em muốn dùng ADO để đưa dữ liệu từ Data vào report.
Em thường dùng phương thức CopyFromRecordset để lấy dữ liệu, thông thường nếu cấu trúc Data và report giống nhau thì không có gì để nói. Nhưng khi report thay đổi cấu trúc một chút thì phương thức này không còn đúng nữa.
Nếu copy từng Recordset thì phải đóng, mở Recordset giải khiến Code dài và phức tạp. Hiện em đang loay hoay với vấn đề này mà chưa có cách giải quyết.

Rất mong anh chị xem xét, giúp em phương pháp tối ưu nhất.
Xin cám ơn

Nếu cấu trúc Data có thay đổi thì thay đổi như thế nào? Có thay đổi tên trường hay không? Nếu không thì bạn vẫn có thể dùng phương thức CopyFromRecordset để lấy dữ liệu.
Cho mình hỏi code của bạn viết như thế nào vậy.

 

[KVP]

Cám ơn bạn
Mình gửi lại File kèm theo code của mình

Ở đây ý mình muốn nói: thông thường cột Mã giống như DATA có thứ tự từ 001-> 010, nhưng nếu Report cột mã thứ tự đó thay đổi, mất đi một số chỉ tiêu 003,005,009 thì khi Copyfromrecordset vào B2 sẽ cho ra toàn bộ dữ liệu của DATA.

Vậy phải thay thế phương thức này hoặc dùng bằng cách khác?

 

[ThuNghi]

Cám ơn bạn
Mình gửi lại File kèm theo code của mình

Ở đây ý mình muốn nói: thông thường cột Mã giống như DATA có thứ tự từ 001-> 010, nhưng nếu Report cột mã thứ tự đó thay đổi, mất đi một số chỉ tiêu 003,005,009 thì khi Copyfromrecordset vào B2 sẽ cho ra toàn bộ dữ liệu của DATA.

Vậy phải thay thế phương thức này hoặc dùng bằng cách khác?

Chưa hiểu ý KVP lắm, có phải sh report muốn ra kết quả cột B và C dạng như Vlookup từ Data?.
Thấy code trên chỉ lấy có 2 cột vào rec nên chưa hiểu ý đồ.
Tồi thì sẽ làm như sau
1/ Dùng ADO lấy toàn bộ data.
2/ Gán Rec -> Arr
3/ Duyệt cột 1 Report để lấy cột 2, 3
Mà hình như dùng ADO có dùng quạn hệ gì đó để chỉ lấy ra rec toàn bộ data theo tiêu chí report.
Tóm lại là yêu cầu cụ thể mới biết.

 

[KVP]

Dạ đúng như anh hiểu đó ah
Có điều dùng ADO để duyệt cột 1 report thì e chưa biết code thế nào . Code của em chỉ lấy được cả mảng dữ liệu của Data chứ không lấy được theo như yêu cầu giống Vlookup

 

[ThuNghi]

Dạ đúng như anh hiểu đó ah
Có điều dùng ADO để duyệt cột 1 report thì e chưa biết code thế nào . Code của em chỉ lấy được cả mảng dữ liệu của Data chứ không lấy được theo như yêu cầu giống Vlookup

Vậy thì viết tiếp 1 code dạng vlookup nhé. Và cũng xử lý từ rec trên theo đúng tinh thần ADO.

 

[KVP]

Anh cho em một ví dụ đc ko

 

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

Cám ơn bạn
Mình gửi lại File kèm theo code của mình

Ở đây ý mình muốn nói: thông thường cột Mã giống như DATA có thứ tự từ 001-> 010, nhưng nếu Report cột mã thứ tự đó thay đổi, mất đi một số chỉ tiêu 003,005,009 thì khi Copyfromrecordset vào B2 sẽ cho ra toàn bộ dữ liệu của DATA.

Vậy phải thay thế phương thức này hoặc dùng bằng cách khác?

Theo mình nghĩ là phải đóng và mở record nhiều lần bằng cách là duyệt qua từng cell dữ liệu sheet Report từ A2:A8 để làm điều kiện lọc. Kết quả tương tứng sẽ cho ra tại 2 cột kế bên.

 

[KVP]

Vậy nếu Report >> 8 dòng thì có khả thi ko anh?

 

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

Vậy nếu Report >> 8 dòng thì có khả thi ko anh?

Theo mình nghỉ chỉ có 8 dòng thì không cần dùng ADO chi cho nhọc, dùng hàm Vlookup được rồi.

 

[ThuNghi]

Anh cho em một ví dụ đc ko

KVP dùng thử code này nhé, có dùng Dic đế lấy danh mục mã duy nhất.

Sub Button1_Click()
Dim strPath As String, mySQL_Dr As String
Dim Cnn As New ADODB.Connection
Dim Rcs As New ADODB.Recordset
Dim iR&, iC&, nR&, sMa$, endr&
Dim recArr(), sArr, rArr, ArrKQ
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")
'-------------------------------------------------------------------------
'Tuy chon Loai BC combobox phu hop SQL text sau menh de Where
strPath = ThisWorkbook.FullName
Set Cnn = New ADODB.Connection
'Tao Ket noi voi file du lieu nguon:
Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & _
                            ";Persist Security Info=False; Extended Properties=Excel 8.0;"
'---------------------------------------------------------------------------
mySQL_Dr = " SELECT Ma, F01,F02  FROM [DATA$] "
Rcs.Open mySQL_Dr, Cnn, adOpenKeyset, adLockOptimistic
'---------------------------------------------------------------------------
With Sheets("Report")
  endr = .Cells(65000, 1).End(xlUp).Row
  .Range("B2:C100").ClearContents
  'gan ma cua rport thanh rArr
  rArr = .Range("A2:A" & endr).Value
End With
'Convert Rec to RecArr va chuyen ngang thanh doc và lay ma duy nhat
recArr = Rcs.GetRows
ReDim sArr(1 To UBound(recArr, 2) + 1, 1 To UBound(recArr) + 1)
For iR = 0 To UBound(recArr, 2)
  sMa = CStr(recArr(0, iR))
  Dic.Add sMa, iR + 1
  For iC = 0 To UBound(recArr, 1)
   sArr(iR + 1, iC + 1) = recArr(iC, iR)
  Next iC
Next iR
'Duyet qua report va lay ket qua
ReDim ArrKQ(1 To UBound(rArr), 1 To 2)
For iR = 1 To UBound(rArr)
  If Len(rArr(iR, 1)) > 0 Then
    sMa = CStr(rArr(iR, 1))
    nR = Dic.Item(sMa)
    For iC = 1 To 2
      ArrKQ(iR, iC) = sArr(nR, iC + 1)
    Next iC
  End If
Next iR
'Gan vao sh
With Sheets("Report")
  .[B2].Resize(UBound(rArr), 2) = ArrKQ
End With
'Refresh lai hai bien cnEx va Rcs:
Rcs.Close: Set Rcs = Nothing
Cnn.Close: Set Cnn = Nothing
Set Dic = Nothing
Erase recArr(), sArr, rArr, ArrKQ
End Sub

 

[KVP]

>>> 8 dòng anh ạ. Thực chất là em muốn biết thuật toán. Ở ví dụ này em đưa dữ liệu là Excel cho đơn giản, ứng dụng của em là lấy dữ liệu từ Access, Excel chỉ dùng làm report nên không Vlookup đc. Muốn vậy em phải đưa data ra một sheets tạm rồi mới Vlookup.
Report thì không cố định bao giờ cả, nên em muốn tìm giải pháp tốt nhất cho ứng dụng của mình bằng ADO

Cám ơn anh Thunghi nhiều, code chạy ngon lành, em chỉnh sửa một chút cho phù hợp với ứng dụng của mình là OK

 

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

>>> 8 dòng anh ạ. Thực chất là em muốn biết thuật toán. Ở ví dụ này em đưa dữ liệu là Excel cho đơn giản, ứng dụng của em là lấy dữ liệu từ Access, Excel chỉ dùng làm report nên không Vlookup đc. Muốn vậy em phải đưa data ra một sheets tạm rồi mới Vlookup.
Report thì không cố định bao giờ cả, nên em muốn tìm giải pháp tốt nhất cho ứng dụng của mình bằng ADO

Cám ơn anh Thunghi nhiều, code chạy ngon lành, em chỉnh sửa một chút cho phù hợp với ứng dụng của mình là OK

Anh ThuNghi can thiệp thô bạo vào nó quá, làm mất "mùi" ADO hết, với lại khi có dữ liệu trùng, trống, hoặc nếu như mã ở sheet Report có mà sheet Data không có sẽ gây lỗi. Ta có thể dùng Query truy vấn với 2 bảng này bình thường giống như bên Access vậy. Bạn thử xem sao nhé.

 

[KVP]

Nếu dùng Query với CSDL acess, report excel mà kết hợp thì em chưa thấy bao giờ.
Giả sử e đưa data ra một sheets tạm rồi truy vấn, câu lệnh SQL liệu có giống Acess không anh. Anh có thể cho ví dụ dc không ạh?
Nhưng dùng được đơn thuần SQL thì vẫn là giải pháp chuẩn ADO nhất

 

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

Nếu dùng Query với CSDL acess, report excel mà kết hợp thì em chưa thấy bao giờ.
Giả sử e đưa data ra một sheets tạm rồi truy vấn, câu lệnh SQL liệu có giống Acess không anh. Anh có thể cho ví dụ dc không ạh?
Nhưng dùng được đơn thuần SQL thì vẫn là giải pháp chuẩn ADO nhất

Bạn thử đoạn truy vấn như sau:

mySQL_Dr = "SELECT [Report$].[Ma], [Data$].[F01], [Data$].[F02] " & _
"FROM [Report$] LEFT JOIN [Data$] ON [Report$].[Ma] = [Data$].[Ma] "

Tham khảo thêm file đính kèm nhé.

 

[KVP]

Quá tuyệt vời.
Nhưng nếu như em đề cập, không đưa Data ra sheets tạm mà để nguyên trong access thì không LEFT JOIN được anh ạh

 

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

Quá tuyệt vời.
Nhưng nếu như em đề cập, không đưa Data ra sheets tạm mà để nguyên trong access thì không LEFT JOIN được anh ạh

Vẫn được bạn à, truy vấn được hết. Nếu không dùng LEFT JOIN thì nó không thể hiện dữ liệu nếu như bảng kia không có dữ liệu. Bạn thử test nhé.

 

[KVP]

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

Nếu đưa data vào Access như File đính kèm thì rõ ràng phép nối INNER JOIN một sheets với một Table mdb là không thể. Anh có thể xem lại dùm em một chút dc không?

 

[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ị.

Nếu đưa data vào Access như File đính kèm thì rõ ràng phép nối INNER JOIN một sheets với một Table mdb là không thể. Anh có thể xem lại dùm em một chút dc không?

Kết nối vào CSDL Access , mà CSDL Access chỉ có bảng Data, không có bảng Report, nên gây lỗi. Hình như không thể ghép 2 kết nối riêng lẽ vào 1. Để thực hiện yêu cầu của bạn thì bạn phải tạo bảng Report trong CSDL Access rồi đưa dữ liệu ĐK vào bảng đó, xong rồi truy vấn, khi đạt kết quả ta xóa hết dữ liệu trong bảng Report đó đi. Bạn thử theo cách này xem nhé.
Xin lỗi mình chưa test theo ý này. Nhưng chắc bạn làm được.
Thân

 

[KVP]

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