ADO - Đưa dữ liệu từ Recordset vào Data Validation (1 người xem)

[tan.ngohoangquoc]

E muốn lấy dữ liệu từ recordset vào DATA Validation thì viết code như thế nào?

Option ExplicitPrivate Module


Public cn As ADODB.Connection
Public rst As ADODB.Recordset
Public cmd As ADODB.Command




Sub CreateConnection()
    Dim ary() As Variant
    
    Set cn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cn.Open ("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};dbq=" & ThisWorkbook.FullName & ";")
    rst.Open ("SELECT DISTINCT Manager from [Managers$]"), cn
    
    If Not rst.EOF Then        
        ary= Application.Transpose(Application.Transpose(rst.GetRows))
    With Sheets("Summary").Range("C2").validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Join(ary, ",")
    End With
    End If
End Sub

Trên mạng thấy đạon code này nhưng sao hem dc

 

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

E muốn lấy dữ liệu từ recordset vào DATA Validation thì viết code như thế nào?

Option ExplicitPrivate Module


Public cn As ADODB.Connection
Public rst As ADODB.Recordset
Public cmd As ADODB.Command




Sub CreateConnection()
    Dim ary() As Variant
    
    Set cn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cn.Open ("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};dbq=" & ThisWorkbook.FullName & ";")
    rst.Open ("SELECT DISTINCT Manager from [Managers$]"), cn
    
    If Not rst.EOF Then        
        ary= Application.Transpose(Application.Transpose(rst.GetRows))
    With Sheets("Summary").Range("C2").validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Join(ary, ",")
    End With
    End If
End Sub

Trên mạng thấy đạon code này nhưng sao hem dc

Báo lỗi như thế nào vậy bạn? Tôi test bình thường mà

 

[tan.ngohoangquoc]

Type mismatch:

ary = Application.Transpose(Application.Transpose(rst.GetRows))

Em không hiểu sao nó cứ báo lỗi cái này ;(

 

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

Type mismatch:

ary = Application.Transpose(Application.Transpose(rst.GetRows))

Em không hiểu sao nó cứ báo lỗi cái này ;(

Dữ liệu vừa số vừa chữ?

 

[tan.ngohoangquoc]

Dữ liệu vừa số vừa chữ?

Dạ em tìm ra nguyên nhân. Do sheet Managers em tô màu sẵn gần 200 dòng mà dữ liệu chỉ có 20 dòng. Nên hình như SQL lấy luôn 1 dòng BLANK đưa vào recordset.

rst.Open ("SELECT DISTINCT Manager from [Managers$] WHERE Manager IS NOT NULL"), cn

Em sữa lại vậy thì chạy ok.

Dữ liệu vừa số vừa chữ?

Thấy anh hỏi vậy mới ngộ ra

Thanks anh Hai Lúa Miền Tây

 

[tan.ngohoangquoc]

Làm xong ok cũng nên đưa cái file có code ADO lên đây để mọi người còn học hỏi nữa chứ lị (file chạy & file dữ liệu nguồn). Chơi hỏi.......mà không kèm file thì thật là ...."uổng" quá đê........

P/s: đang tìm hiểu món ADO nên .......

File Đây Có một file thui

 

[tan.ngohoangquoc]

Nghe nói tới ADO tưởng là đang lấy dữ liệu từ File đóng nên mới...........hỏi để học. Ai zè có 1 file vậy mà cần chi chơi "Đao To Búa Lớn" vậy nhỉ?
P/s: Cơ mà code ADO trên file của bạn tôi chạy hok có được (phải chăng là cái Office 2013 64bit nó hành thằng ADO này).

Với file của bạn thì dùng cái DIC cho nó đơn giản. ADO ta chỉ để dành chơi với việc lấy dữ liệu từ file đóng đi cho nó Pờ Rồ, Pờ Ao,...

Public Sub AddDataValidation()
Dim DL(), I As Long, Dic As Object
DL = Range(Sheet7.[B2], Sheet7.[B6500].End(3)).Value
Set Dic = CreateObject("Scripting.Dictionary")
    For I = 1 To UBound(DL)
        Dic(DL(I, 1)) = ""
    Next
        Range("C2").validation.Delete
        Range("C2").validation.Add 3, , , Join(Dic.keys, ",")
Set Dic = Nothing
End Sub

Máy mình cũng excel 2013 64bit

Cơ mà việc dùng Dic thì mình biết rùi

Đâu phải nhất thiết phải lấy file đóng mới dùng ADO

Những cái đơn giản này mình làm ADO vì muốn học nó

File đưa lên đây nó nhỏ xíu vậy thui File gốc nó hem nhỏ vại đâu

 

[tan.ngohoangquoc]

Mình chọn Microsoft ActiveX Data Objects 2.8 Library

Ủa mà hình như mình chọn cái này và khi share file cho người khác thì vẫn sài dc mà nhỉ. Hem rõ lắm.

Nói chung là lúc xài ADO này thì chọn cái Microsoft ActiveX Data Objects X.X Library

 

[tan.ngohoangquoc]

Biết lỗi tai sao rồi. Do bạn tách cái Open ra 1 sub riêng. Muốn chạy được Sub ValidationSummary() thì phải chạy cái OpenConnection() trước.... Nếu không sẽ bị lỗi.

Cơ mà sao không đưa luôn cái Open & Close đó vào Sub Main luôn nhỉ? Tách ra mà trong Sub Main bạn không Call cái OpenConnection() mà vẫn chạy được hả ta???

Mình có Call OpenConnection từ lúc mở file mà. Trong sự kiện workbook_Open á.

 

[tan.ngohoangquoc]

Hic không để ý. Cứ mở file lên chỉ chạy sub Main của bạn thôi. Cơ mà cho tôi hói ở món ADO này nó căn cứ như thế nào để Query đúng cái Title vậy nhỉ, không thấy khai báo là từ Row nào hết trơn mà nó tự động hiểu được cái Title hả ta???

Về cái này thì hy vọng có thầy nào đi ngang giải thích hoặc bạn cố gắng search trên diễn đàn thử để hiểu.

Còn trong bài mình thì khi mình khai báo sheet [Managers$] trong câu lệnh query, excel (hay cái gì đó ) sẽ tìm vùng dữ liệu có trong sheet này và xác định row đầu của vùng dữ liệu là header.

À bạn nên xem các provider này nữa

1. Providers: 	Microsoft Jet OLE DB 4.0: "provider=microsoft.jet.oledb.4.0; data source=DuongDanDenFile; extended properties=""Excel 8.0"";"
	Microsoft ACE OLEDB 12.0: "provider=microsoft.ace.oledb.12.0; data source=DuongDanDenFile; extended properties=""Excel 12.0"";"


2. ODBC drivers
	Microsoft Excel ODBC Driver: Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=DuongDanDenFile.xls;
	Microsoft Excel 2007 ODBC Driver: Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=DuongDanDenExcelFile;
3. ODBC 
	ODBC (Open Database Connectivity): "Provider=MSDASQL.1;DSN=Excel Files;DBQ=DuongDanDenFileExcel;"

 

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

Nghe nói tới ADO tưởng là đang lấy dữ liệu từ File đóng nên mới...........hỏi để học. Ai zè có 1 file vậy mà cần chi chơi "Đao To Búa Lớn" vậy nhỉ?
P/s: Cơ mà code ADO trên file của bạn tôi chạy hok có được (phải chăng là cái Office 2013 64bit nó hành thằng ADO này).

Với file của bạn thì dùng cái DIC cho nó đơn giản. ADO ta chỉ để dành chơi với việc lấy dữ liệu từ file đóng đi cho nó Pờ Rồ, Pờ Ao,...

Public Sub AddDataValidation()
Dim DL(), I As Long, Dic As Object
DL = Range(Sheet7.[B2], Sheet7.[B6500].End(3)).Value
Set Dic = CreateObject("Scripting.Dictionary")
    For I = 1 To UBound(DL)
        Dic(DL(I, 1)) = ""
    Next
        Range("C2").validation.Delete
        Range("C2").validation.Add 3, , , Join(Dic.keys, ",")
Set Dic = Nothing
End Sub

Hãy tập vận dụng ADO cho dù nó nhỏ, từ nhỏ ta có thể làm cái lớn hơn. Nếu không vận dụng thì sẽ không bao giờ dùng nó. Bạn nên nhớ rằng ADO là công cụ rất mạnh để đi đến lập trình chuyên nghiệp.

 

[VetMini]

Tuy nhiên phải cẩn thận khi sử dụng ADO để đọc file đang mở.
Khi ADO kêt nối với file đang đóng, nó được quyền sử dụng tuỳ thích cho nên lúc ta đóng connection thì mọi tài nguyên đều được giải phóng nghiêm chỉnh.
Khi ADO kết nối với file đang mở, nhất là chính file đang gọi code, thì quyền sử dụng bị giới hạn cho nên lúc ta đóng connection thì có khả năng một số tài nguyên vẫn còn bị kẹt. Điều này dẫn đến việc sau vài lần kết nối và đóng, Excel có khả năng bị treo.

Tôi chỉ cảnh báo thôi. Thông thường thì code của quý vị chỉ chạy 1 lượt, tức là chỉ kết nối 1 lần cho nên lỗi này không hề thấy xảy ra.