Cách truy vấn dữ liệu trên cùng file không mở kết nối (ADODB.Connection) (1 người xem)

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

Thông thường ta truy vấn dữ liệu bằng ADO trên cùng file thì ta vẫn phải mở kết nối đến chính file thực thi đó. Điều này làm giảm đi tốc độ khi chạy code.
Hôm nay mình xin chia sẻ với các bạn cách truy vấn dữ liệu mà không cần mở kết nối trên cùng file thực thi.

Sub LocDL_HLMT()
    Dim rst As Object, vArray As Variant, i As Long, thoigian As Long 'Khai bao bien
    Set rst = CreateObject("ADODB.Recordset") ' Khoi tao Recordset
    vArray = Sheet1.Range("A2:D10001").Value ' Du lieu nguon vao mang
    thoigian = Timer()
    With rst
        'Khai bao va mo Recorset
        .Fields.Append "ID", 3
        .Fields.Append "MatID", 200, 10
        .Fields.Append "Balance", 3
        .Open
        'Dua du lieu nguon tu mang da set vao Recordset
        For i = LBound(vArray) To UBound(vArray)
            .AddNew
            .Fields("ID").Value = vArray(i, LBound(vArray))
            .Fields("MatID").Value = vArray(i, LBound(vArray) + 1)
            .Fields("Balance").Value = vArray(i, LBound(vArray) + 3)
            .Update
        Next
        'Loc du lieu trong Recordset
        .Filter = "Balance " & Sheet2.Range("B1")
    End With
    Sheet2.Range("A2:D11000").ClearContents 'Xoa vung du lieu
    Sheet2.Range("A4").CopyFromRecordset rst 'Do du lieu tu Recordset da loc xuong sheet
    MsgBox Timer - thoigian
    rst.Close
    Set rst = Nothing
End Sub

 

[Hoàng Trọng Nghĩa]

Quá nhanh, quá nguy hiểm luôn! Rất tuyệt vời! Với một file Excel cần xuất dữ liệu từ Access chắc không làm được như vậy đâu em nhỉ?

.Fields.Append "MatID", 200, 10

Anh chưa hiểu số 200 và số 10 là cái gì, em vui lòng giải thích giúp anh nhé! Thank you!

 

[FatBear]

thực sự quá tốc độ )

 

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

Quá nhanh, quá nguy hiểm luôn! Rất tuyệt vời! Với một file Excel cần xuất dữ liệu từ Access chắc không làm được như vậy đâu em nhỉ?

.Fields.Append "MatID", 200, 10


Anh chưa hiểu số 200 và số 10 là cái gì, em vui lòng giải thích giúp anh nhé! Cảm ơn!

Đúng chính xác là tốc độ tuyệt vời đó anh
Là kiểu dữ liệu thôi anh, 200 là kiểu chuỗi, 10 là độ dài chuỗi đó anh.
Anh xem thêm bảng bên dưới nhé.

Constant	Value	Description
adArray	0x2000	Combine with another data type to indicate that the other data type is an array
adBigInt	20	8-byte signed integer
adBinary	128	Binary
adBoolean	11	True or false Boolean
adBSTR	8	Null-terminated character string
adChapter	136	4-byte chapter value for a child recordset
adChar	129	String
adCurrency	6	Currency format
adDate	7	Number of days since 12/30/1899
adDBDate	133	YYYYMMDD date format
adDBTime	134	HHMMSS time format
adDBTimeStamp	135	YYYYMMDDHHMMSS date/time format
adDecimal	14	Number with fixed precision and scale
adDouble	5	Double precision floating-point
adEmpty	0	no value
adError	10	32-bit error code
adFileTime	64	Number of 100-nanosecond intervals since 1/1/1601
adGUID	72	Globally unique identifier
adIDispatch	9	Currently not supported by ADO
adInteger	3	4-byte signed integer
adIUnknown	13	Currently not supported by ADO
adLongVarBinary	205	Long binary value
adLongVarChar	201	Long string value
adLongVarWChar	203	Long Null-terminates string value
adNumeric	131	Number with fixed precision and scale
adPropVariant	138	PROPVARIANT automation
adSingle	4	Single-precision floating-point value
adSmallInt	2	2-byte signed integer
adTinyInt	16	1-byte signed integer
adUnsignedBigInt	21	8-byte unsigned integer
adUnsignedInt	19	4-byte unsigned integer
adUnsignedSmallInt	18	2-byte unsigned integer
adUnsignedTinyInt	17	1-byte unsigned integer
adUserDefined	132	User-defined variable
adVarBinary	204	Binary value
adVarChar	200	String
adVariant	12	Automation variant
adVarNumeric	139	Variable width exact numeric with signed scale
adVarWChar	202	Null-terminated Unicode character string
adWChar	130	Null-terminated Unicode character string

 

[ongke0711]

Cách của bạn HLMT là tạo ADO recordset lưu trên bộ nhớ máy do đó việc truy xuất ngay trên memory sẽ nhanh hơn truy xuất trên ổ cứng. Cách này rất hay và nhanh để xử lý một tác vụ nào đó xong đóng và giải phóng bộ nhớ.
Cách này cũng thường dùng khi truy vấn dữ liệu từ SQL server lưu tạm để xử lý (sắp xếp, vấn tin, cập nhật...) rồi đồng bộ về lại sql server.

 

[Nguyễn Duy Tuân]

Cách của HLMT trình bày trên là đẩy mảng dữ liệu từ sheet vào recodset, từ đây dùng Filter - lọc. Nếu bài toán của người dùng là trích lọc dữ liệu trên sheet thì hoàn toàn ok. Tuy nhiên đây không phải SQL nên sẽ gặp phải các cấn đề sau:
- Mất thời gian lưu array vào recordset ban đầu. Mảng data có thể lấy từ sheet, còn lấy từ csdl khác như Access thì nên lấy bằng SQL luôn chứ không dùng Array vì sẽ mất hai lần làm không cần thiết (câu hỏi của a Hoàng Trọng Nghĩa)
- Chỉ áp dụng cho bài toán Filter trên data nguồn đưa vào dạng đơn lẻ không quan hệ. Vì không phải cách SQL nên không thực hiện được việc ORDER BY, GROUP BY, HAVING, các kết nối bảng với INNER JOIN, LEFT JOIN,...

Như vậy bài toán trên nên dùng trong phạm vi dữ liệu nguồn là Excel, một bảng nguồn không quan hệ. Bài toán gọi là lọc dữ liệu trong mảng.

 

[FatBear]

.Fields.Append "ID", 3
.Fields.Append "MatID", 200, 10
.Fields.Append "Balance", 3

Bác HLMT cho e hỏi chút, ở dòng 2 có thêm "10" ,vậy tại sao hai dòng ID và balance lại không có thêm giá trị đó ạ.Em cảm ơn

 

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

.Fields.Append "ID", 3
.Fields.Append "MatID", 200, 10
.Fields.Append "Balance", 3

Bác HLMT cho e hỏi chút, ở dòng 2 có thêm "10" ,vậy tại sao hai dòng ID và balance lại không có thêm giá trị đó ạ.Em cảm ơn

Bạn xem lại bài 4 tôi đã giải thích rồi nhé bạn.

 

[FatBear]

Bạn xem lại bài 4 tôi đã giải thích rồi nhé bạn.

bác chưa hiểu đúng ý e rồi. ý của e là tại sao ở trường MatID có thêm điều kiện 10 ( tra bảng 32-bit error code ), còn ở 2 trường ID và blance lại không có

 

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

bác chưa hiểu đúng ý e rồi. ý của e là tại sao ở trường MatID có thêm điều kiện 10 ( tra bảng 32-bit error code ), còn ở 2 trường ID và blance lại không có

Trường ID và Balance là kiểu Int, còn trường MatID là kiểu String, 10 là độ dài cho chuỗi đó bạn

 

[Kiều Mạnh]

1/ Mạnh nghỉ trên cùng 1 File xài mảng đi cho gọn và nhanh ... trừ khi lấy dữ liệu từ 1 file đóng

2/ Mà xài ADO trong trường hợp này dễ gì nhanh hơn cái Mảng trên Sheet ?????????????

 

[FatBear]

Trường ID và Balance là kiểu Int, còn trường MatID là kiểu String, 10 là độ dài cho chuỗi đó bạn

à. vâng. e cảm ơn

 

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

1/ Mạnh nghỉ trên cùng 1 File xài mảng đi cho gọn và nhanh ... trừ khi lấy dữ liệu từ 1 file đóng

2/ Mà xài ADO trong trường hợp này dễ gì nhanh hơn cái Mảng trên Sheet ?????????????

Nếu lọc và sắp xếp thì nó sẽ đơn giản hơn việc xử lý trong mảng đó anh.

 

[Kiều Mạnh]

Nếu lọc và sắp xếp thì nó sẽ đơn giản hơn việc xử lý trong mảng đó anh.

để mai mốt rảnh thử viết thành 1 cái hàm dựa trên code bài 1 xem sao ???? gõ [A1] = Ham(tham so1, tham so2)

không biết gõ cái cộp 1 cái nó có ra không nữa

 

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

để mai mốt rảnh thử viết thành 1 cái hàm dựa trên code bài 1 xem sao ???? gõ [A1] = Ham(tham so1, tham so2)

không biết gõ cái cộp 1 cái nó có ra không nữa

Vậy nếu anh viết và xử lý trong mảng cho trường hợp lọc dữ liệu theo điều kiện và sắp xếp thử xem coi cái nào đơn giản hơn nhé.

 

[ongke0711]

Nếu lọc và sắp xếp thì nó sẽ đơn giản hơn việc xử lý trong mảng đó anh.

Tôi thì chưa rành về mảng nhưng đối với "in- memory ADO recordset" này thì nó như một cái table ảo có đầy đủ tên trường (cột) nên bạn có thể dùng các câu lệnh SQL để lọc, sắp xếp. Rs.Sort ="ID"

 

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

Tôi thì chưa rành về mảng nhưng đối với "in- memory ADO recordset" này thì nó như một cái table ảo có đầy đủ tên trường (cột) nên bạn có thể dùng các câu lệnh SQL để lọc, sắp xếp. Rs.Sort ="ID"

Đó là lý do mà tôi đã nói ở trên rằng nó sẽ rất đơn giản

 

[Kiều Mạnh]

Đó là lý do mà tôi đã nói ở trên rằng nó sẽ rất đơn giản

nếu lấy dữ liệu từ 1 file đang đóng lên ta phải khai báo lại như thế nào nhỉ ?

 

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

nếu lấy dữ liệu từ 1 file đang đóng lên ta phải khai báo lại như thế nào nhỉ ?

Chỉ trên file hiện hành, còn file đóng thì mở kết nối rồi anh

 

[leonguyenz]

Chỉ trên file hiện hành, còn file đóng thì mở kết nối rồi anh

Anh giới thiệu thêm ứng dụng của truy vấn này được không?
Vì nếu để lọc dữ liệu thì AdvanceFilter có vẻ đơn giản, dễ áp dụng, dù nó có chậm 1 chút.
Em test AdvanceFilter 10k dòng thì thời gian cũng tương đương, còn 120k dòng thì đúng là không bằng cách truy vấn trên.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tg As Long
Tg = Timer()
If Target.Address = "$F$2" Then
   [A1:D150000].AdvancedFilter 2, [F1:F2], [H1:J1]
End If
MsgBox Timer - Tg
End Sub

 

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

Anh giới thiệu thêm ứng dụng của truy vấn này được không?
Vì nếu để lọc dữ liệu thì AdvanceFilter có vẻ đơn giản, dễ áp dụng, dù nó có chậm 1 chút.
Em test AdvanceFilter 10k dòng thì thời gian cũng tương đương, còn 120k dòng thì đúng là không bằng cách truy vấn trên.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tg As Long
Tg = Timer()
If Target.Address = "$F$2" Then
   [A1:D150000].AdvancedFilter 2, [F1:F2], [H1:J1]
End If
MsgBox Timer - Tg
End Sub

Thảo thử với 1 triệu dòng mới thấy sự khác biệt.

 

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

Sắp xếp và lọc duy nhất:

Sub LocDL_HLMT()
    Dim rst As Object, vArray As Variant, i As Long, thoigian As Long 'Khai bao bien
    Dim strID As String, strMatID As String, strDup As String
    Set rst = CreateObject("ADODB.Recordset") ' Khoi tao Recordset
    vArray = Sheet1.Range("A2:D10001").Value ' Du lieu nguon vao mang
    thoigian = Timer()
    With rst
        'Khai bao va mo Recorset
        .Fields.Append "ID", 3
        .Fields.Append "MatID", 200, 10
        .Fields.Append "Balance", 3
        .Open
        'Dua du lieu nguon tu mang da set vao Recordset
        For i = LBound(vArray) To UBound(vArray)
            .AddNew
            .Fields("ID").Value = vArray(i, LBound(vArray))
            .Fields("MatID").Value = vArray(i, LBound(vArray) + 1)
            .Fields("Balance").Value = vArray(i, LBound(vArray) + 3)
            .Update
        Next
        'Loc du lieu trong Recordset
        .Filter = "Balance " & Sheet2.Range("B1")
        .Sort = "ID desc" 'Sap xep
        .MoveFirst
        strDup = ""
        Do Until .EOF
            strID = .Fields.Item("ID")
            strMatID = .Fields.Item("MatID")
            If (strDup = strID & "-" & strMatID) Then
                .Delete
            End If
            strDup = strID & "-" & strMatID
            .Update
            .MoveNext
        Loop
        .MoveFirst
    End With
    Sheet2.Range("A2:D11000").ClearContents 'Xoa vung du lieu
    Sheet2.Range("A4").CopyFromRecordset rst   'Do du lieu tu Recordset da loc xuong sheet
    MsgBox Timer - thoigian
    rst.Close
    Set rst = Nothing
End Sub

 

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

Lọc, sắp xếp, tính tổng

Sub LocDL_HLMT()
    Dim rst As Object, vArray As Variant, i As Long, thoigian As Long 'Khai bao bien
    Dim strID As String, strMatID As String, strDup As String
    Dim intTotal As Integer
    Set rst = CreateObject("ADODB.Recordset") ' Khoi tao Recordset
    vArray = Sheet1.Range("A2:D10001").Value ' Du lieu nguon vao mang
    thoigian = Timer()
    With rst
        'Khai bao va mo Recorset
        .Fields.Append "ID", 3
        .Fields.Append "MatID", 200, 10
        .Fields.Append "Balance", 3
        .Open
        'Dua du lieu nguon tu mang da set vao Recordset
        For i = LBound(vArray) To UBound(vArray)
            .AddNew
            .Fields("ID").Value = vArray(i, LBound(vArray))
            .Fields("MatID").Value = vArray(i, LBound(vArray) + 1)
            .Fields("Balance").Value = vArray(i, LBound(vArray) + 3)
            .Update
        Next
        'Loc du lieu trong Recordset
        .Filter = "Balance " & Sheet2.Range("B1")
        .Sort = "ID" 'Sap xep
        .MoveFirst
        strDup= ""
        Do Until .EOF
            strID = .Fields.Item("ID")
            strMatID = .Fields.Item("MatID")
            intTotal = 0
            If (strDup = strID & "-" & strMatID) Then
                intTotal = intTotal + .Fields.Item("Balance")
                .MovePrevious
                .Fields.Item("Balance") = intTotal + .Fields.Item("Balance")
                .MoveNext
                .Delete
            End If
            strDup = strID & "-" & strMatID
            .Update
            .MoveNext
        Loop
        .MoveFirst
    End With
    Sheet2.Range("A2:D11000").ClearContents 'Xoa vung du lieu
    Sheet2.Range("A4").CopyFromRecordset rst   'Do du lieu tu Recordset da loc xuong sheet
    MsgBox Timer - thoigian
    rst.Close
    Set rst = Nothing
End Sub

 

[thuyyeu99]

Em cũng đang nghiên cứu giải pháp xu lý dữ liệu cho nhanh ma chưa có hướng xử lý lý đây

 

[ongke0711]

Tôi cũng mới ngâm cứu học hỏi VBA của excel nên cũng chưa rành lắm. Có một thắc mắc là phần lớn tôi thấy mọi người dùng mảng nhiều hơn là dùng đối tượng Recordset trong excel. Mảng xử lý nhanh hơn, linh động hơn trong ứng dụng Excel?

 

[thuyyeu99]

Tôi cũng mới ngâm cứu học hỏi VBA của excel nên cũng chưa rành lắm. Có một thắc mắc là phần lớn tôi thấy mọi người dùng mảng nhiều hơn là dùng đối tượng Recordset trong excel. Mảng xử lý nhanh hơn, linh động hơn trong ứng dụng Excel?

Theo mình nghĩ chắc mãng linh động dễ hình dung, dễ khai báo, dễ gán dữ liệu, có thể dễ xử lý hơn Recordset

 

[Nguyễn Duy Tuân]

Tôi cũng mới ngâm cứu học hỏi VBA của excel nên cũng chưa rành lắm. Có một thắc mắc là phần lớn tôi thấy mọi người dùng mảng nhiều hơn là dùng đối tượng Recordset trong excel. Mảng xử lý nhanh hơn, linh động hơn trong ứng dụng Excel?

Tùy vào tính huống mà ta lựa chọn khi nào dùng SQL, khi nào dùng Array. Ví dụ bạn muốn tạo công thức cho phép người dùng đặt điều kiện lọc tùy ý trên một mảng thì dùng như bài #1 topic này sẽ dễ dàng hơn. Nếu bạn có thể viết lọc trơn trên VBA nhưng (không dùng chức năng Advanced Filter) thì bạn phải có giải thuật rất tốt mới cho phép người sử dụng tùy biến đặt điều kiện. Nếu bạn cần dùng truy vấn chuẩn CSDL, các bảng quan hệ vào nhau, gộp dữ liệu với các hàm, đặt điều kiện với các hàm thống kê thì phải dùng SQL. Vấn đề nữa là file data nguồn nằm ở máy tính trong mạng thì cũng cần phải dùng SQL, vì dùng Array luôn phải mở file Excel nguồn,...

 

[VetMini]

1/ Mạnh nghỉ trên cùng 1 File xài mảng đi cho gọn và nhanh ... trừ khi lấy dữ liệu từ 1 file đóng

2/ Mà xài ADO trong trường hợp này dễ gì nhanh hơn cái Mảng trên Sheet ?????????????

Mảng chỉ là... mảng, một cấu trúc căn bản.
Để mảng có thể làm những việc khác, ngừoi ta lồng nó vào các hình thức khác, điển hình là ArrayList trong dot net. Với ArrayList, ta có thể sort.

Ở đây, adodb.recordset được dùng như là một dạng Table (table của Excel, không phải Access)
Trong thớt này, tác giả giới thiệu cách lợi dụng hai thủ tục của ado recordset để filter và sort.
Muốn làm được việc này, code sử dụng array căn bản phải gọi hai hàm nào đó. Nếu hàm chưa có sẵn trong thư viện, bạn phải import chúng.

Cách của HLMT trình bày trên là đẩy mảng dữ liệu từ sheet vào recodset, từ đây dùng Filter - lọc. Nếu bài toán của người dùng là trích lọc dữ liệu trên sheet thì hoàn toàn ok. Tuy nhiên đây không phải SQL nên sẽ gặp phải các cấn đề sau:
- Mất thời gian lưu array vào recordset ban đầu. Mảng data có thể lấy từ sheet, còn lấy từ csdl khác như Access thì nên lấy bằng SQL luôn chứ không dùng Array vì sẽ mất hai lần làm không cần thiết (câu hỏi của a Hoàng Trọng Nghĩa)
- Chỉ áp dụng cho bài toán Filter trên data nguồn đưa vào dạng đơn lẻ không quan hệ. Vì không phải cách SQL nên không thực hiện được việc ORDER BY, GROUP BY, HAVING, các kết nối bảng với INNER JOIN, LEFT JOIN,...

Như vậy bài toán trên nên dùng trong phạm vi dữ liệu nguồn là Excel, một bảng nguồn không quan hệ. Bài toán gọi là lọc dữ liệu trong mảng.

ADODB chỉ là một API. Không thể so sánh với SQL là một ngôn ngữ tiêu chuẩn, truy vấn qua bộ máy của CSDL (Access, SQL Server, vv...)

@Thớt:
Nếu tôi nhớ không lầm thì có thể đưa dữ liệu vào recordset bằng cách ngắn hơn như sau:
'Dua du lieu nguon tu mang da set vao Recordset
Dim fArray
fArray = Array("ID", "MatID", "Balance") ' hoặc Array(0, 1, 2)
For i = LBound(vArray) To UBound(vArray)
.AddNew fArray, Array(vArray(i, 1), vArray(i, 2), vArray(i,4))
Next
.Update

 

[Nguyễn Duy Tuân]

ADODB chỉ là một API. Không thể so sánh với SQL là một ngôn ngữ tiêu chuẩn, truy vấn qua bộ máy của CSDL (Access, SQL Server, vv...)

Kiến thức Array và SQL thì em không lạ gì mà anh. Trên là em chỉ ra tùy vào tình huống để sử dụng thôi.

 

[ongke0711]

Cũng xin góp vui với đề tài này.
Tôi demo một cách dùng khác của Recordset nhưng có tạo Connection.
Theo cách của anh HLMT là tạo một table ảo (ADO recordset) trên bộ nhớ và xử lý Filter, Sort. Cách này theo tôi có một điểm yếu đó là nó sẽ tải toàn bộ dữ liệu lên Recordset (10.000 dòng trong ví dụ này). Điều này sẽ ảnh hưởng đến bộ nhớ rõ rệt nếu số lượng record lên hàng triệu.
Cách của tôi đang làm thì do không rành về mảng nên chỉ thuần tuý dùng Recordset và name range của Excel để gán dữ liệu vào recordset. Dữ liệu được chọn lọc trước khi ghi lên Recordset nên về mặt bộ nhớ sẽ giảm áp lực hơn.
Các bạn tham khảo góp ý nhé.

- Cách làm này phải tốn thêm một bước là tạo cái Name range và cập nhật nó mỗi khi có thay đổi thêm số dòng vào dữ liệu.
- Tôi có viết cái hàm để cập nhật lại name range.

Option Explicit

Function createDynamicNamedRange() As Boolean
'--------------------------------------------------------------------------------------
'# Áp dung khi da xac dinh so cot cua range, hàm chi cap nhat dong cho so dòng cua range
'--------------------------------------------------------------------------------------

    Dim sht                     As Worksheet
    Dim lngFirstRowRng          As Long
    Dim lngLastRowRng           As Long
    Dim lngFirstColRng          As Long
    Dim lngLastColRng           As Long
    Dim myDynamicNamedRange     As Range
    Dim sRngName                As String

    Set sht = ThisWorkbook.Worksheets("Sheet1")

    'Khai bao dòng/côt dau tiên cua Range
    lngFirstRowRng = 1
    lngFirstColRng = 1

    'dat ten Name range tuy ý
    sRngName = "TonKhoVT"

    With sht.Cells
        lngLastRowRng = sht.Cells(Rows.Count, "D").End(xlUp).Row
        lngLastColRng = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set myDynamicNamedRange = .Range(.Cells(lngFirstRowRng, lngFirstColRng), .Cells(lngLastRowRng, lngLastColRng))
    End With

    ThisWorkbook.Names.Add Name:=sRngName, RefersTo:=myDynamicNamedRange

End Function

- Code cho Sub lọc dữ liệu:

Sub LocRst()
On Error GoTo LocRst_Err
    Dim oCnn As Object
    Dim oRst As Object
    Dim sRngName As String, fileExcel As String
    Dim s, s1, s2, s3 As String     'Chuoi dieu kien loc
    Dim thoigian As Long

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Set oCnn = CreateObject("ADODB.Connection")
    Set oRst = CreateObject("ADODB.Recordset")

    thoigian = Timer()

    fileExcel = ThisWorkbook.FullName
    With oCnn
        Select Case CLng(Application.Version)
        Case 11   'Excel 2003
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=" & fileExcel & ";" & _
                                "Extended Properties=""Excel 8.0;HDR=Yes;"";"
        Case Is >= 12    'Excel 2007 tro lên"
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & fileExcel & ";" & _
                                "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"";"
        End Select
        .Open
    End With

    sRngName = "TonKhoVT"

    'Tong hop dieu kien loc
    s = "SELECT * FROM [" & sRngName & "] WHERE 1=1"
    s1 = " AND [" & Sheet3.Range("A1") & "]" & Sheet3.Range("B1")                   'Balance
    s2 = " AND [" & Sheet3.Range("A2") & "] =" & Sheet3.Range("B2")                 'ID
    s3 = " AND [" & Sheet3.Range("A3") & "] ='" & Sheet3.Range("B3") & "'"    'Remark
    If Not IsEmpty(Sheet3.Range("B1")) Then
        s = s & s1
    End If
    If Not IsEmpty(Sheet3.Range("B2")) Then
        s = s & s2
    End If
    If Not IsEmpty(Sheet3.Range("B3")) Then
        s = s & s3
    End If
    s = s & " ORDER BY [ID]"
    'Debug.Print s

    oRst.Open s, oCnn, adOpenStatic, adLockOptimistic, adCmdText

    Sheet3.Range("A7:D11000").ClearContents    'Xoa vung du lieu
    Sheet3.Range("A7").CopyFromRecordset oRst   'Do du lieu tu Recordset da loc xuong sheet
    Sheet3.Range("A5") = "Tong so record tim thay: " & oRst.RecordCount
    MsgBox Timer - thoigian

LocRst_Exit:
    oRst.Close
    Set oRst = Nothing
    If Not oCnn Is Nothing Then
        oCnn.Close
        Set oCnn = Nothing
    End If
    Exit Sub

LocRst_Err:
    Select Case Err.Number
    Case -2147217900
        MsgBox "Sai dieu kien loc du lieu!" & vbNewLine & vbNewLine & Err.Description, vbCritical, "Thông báo"
        Exit Sub
    Case Else
        MsgBox "Loi: " & Err.Number & vbNewLine & "Noi dung loi: " & Err.Description, vbExclamation, "Thông báo"
        Exit Sub
End Select

End Sub

 

[thuyyeu99]

Tốc độ hàm của em chắc cũng không thua đâu

 

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

Mảng chỉ là... mảng, một cấu trúc căn bản.
Để mảng có thể làm những việc khác, ngừoi ta lồng nó vào các hình thức khác, điển hình là ArrayList trong dot net. Với ArrayList, ta có thể sort.

Ở đây, adodb.recordset được dùng như là một dạng Table (table của Excel, không phải Access)
Trong thớt này, tác giả giới thiệu cách lợi dụng hai thủ tục của ado recordset để filter và sort.
Muốn làm được việc này, code sử dụng array căn bản phải gọi hai hàm nào đó. Nếu hàm chưa có sẵn trong thư viện, bạn phải import chúng.



ADODB chỉ là một API. Không thể so sánh với SQL là một ngôn ngữ tiêu chuẩn, truy vấn qua bộ máy của CSDL (Access, SQL Server, vv...)

@Thớt:
Nếu tôi nhớ không lầm thì có thể đưa dữ liệu vào recordset bằng cách ngắn hơn như sau:
'Dua du lieu nguon tu mang da set vao Recordset
Dim fArray
fArray = Array("ID", "MatID", "Balance") ' hoặc Array(0, 1, 2)
For i = LBound(vArray) To UBound(vArray)
.AddNew fArray, Array(vArray(i, 1), vArray(i, 2), vArray(i,4))
Next
.Update

Vì chưa thấy có trên GPE nên em muốn giới thiệu cho anh em phát triển thêm đó anh. Càng chi tiết càng dễ hình dung, sau này anh em quen và hiểu rõ rồi thì tùy biến mà dùng anh.

Cũng xin góp vui với đề tài này.
Tôi demo một cách dùng khác của Recordset nhưng có tạo Connection.
Theo cách của anh HLMT là tạo một table ảo (ADO recordset) trên bộ nhớ và xử lý Filter, Sort. Cách này theo tôi có một điểm yếu đó là nó sẽ tải toàn bộ dữ liệu lên Recordset (10.000 dòng trong ví dụ này). Điều này sẽ ảnh hưởng đến bộ nhớ rõ rệt nếu số lượng record lên hàng triệu.
Cách của tôi đang làm thì do không rành về mảng nên chỉ thuần tuý dùng Recordset và name range của Excel để gán dữ liệu vào recordset. Dữ liệu được chọn lọc trước khi ghi lên Recordset nên về mặt bộ nhớ sẽ giảm áp lực hơn.
Các bạn tham khảo góp ý nhé.

- Cách làm này phải tốn thêm một bước là tạo cái Name range và cập nhật nó mỗi khi có thay đổi thêm số dòng vào dữ liệu.
- Tôi có viết cái hàm để cập nhật lại name range.

Option Explicit

Function createDynamicNamedRange() As Boolean
'--------------------------------------------------------------------------------------
'# Áp dung khi da xac dinh so cot cua range, hàm chi cap nhat dong cho so dòng cua range
'--------------------------------------------------------------------------------------

    Dim sht                     As Worksheet
    Dim lngFirstRowRng          As Long
    Dim lngLastRowRng           As Long
    Dim lngFirstColRng          As Long
    Dim lngLastColRng           As Long
    Dim myDynamicNamedRange     As Range
    Dim sRngName                As String

    Set sht = ThisWorkbook.Worksheets("Sheet1")

    'Khai bao dòng/côt dau tiên cua Range
    lngFirstRowRng = 1
    lngFirstColRng = 1

    'dat ten Name range tuy ý
    sRngName = "TonKhoVT"

    With sht.Cells
        lngLastRowRng = sht.Cells(Rows.Count, "D").End(xlUp).Row
        lngLastColRng = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set myDynamicNamedRange = .Range(.Cells(lngFirstRowRng, lngFirstColRng), .Cells(lngLastRowRng, lngLastColRng))
    End With

    ThisWorkbook.Names.Add Name:=sRngName, RefersTo:=myDynamicNamedRange

End Function

- Code cho Sub lọc dữ liệu:

Sub LocRst()
On Error GoTo LocRst_Err
    Dim oCnn As Object
    Dim oRst As Object
    Dim sRngName As String, fileExcel As String
    Dim s, s1, s2, s3 As String     'Chuoi dieu kien loc
    Dim thoigian As Long

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Set oCnn = CreateObject("ADODB.Connection")
    Set oRst = CreateObject("ADODB.Recordset")

    thoigian = Timer()

    fileExcel = ThisWorkbook.FullName
    With oCnn
        Select Case CLng(Application.Version)
        Case 11   'Excel 2003
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=" & fileExcel & ";" & _
                                "Extended Properties=""Excel 8.0;HDR=Yes;"";"
        Case Is >= 12    'Excel 2007 tro lên"
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & fileExcel & ";" & _
                                "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"";"
        End Select
        .Open
    End With

    sRngName = "TonKhoVT"

    'Tong hop dieu kien loc
    s = "SELECT * FROM [" & sRngName & "] WHERE 1=1"
    s1 = " AND [" & Sheet3.Range("A1") & "]" & Sheet3.Range("B1")                   'Balance
    s2 = " AND [" & Sheet3.Range("A2") & "] =" & Sheet3.Range("B2")                 'ID
    s3 = " AND [" & Sheet3.Range("A3") & "] ='" & Sheet3.Range("B3") & "'"    'Remark
    If Not IsEmpty(Sheet3.Range("B1")) Then
        s = s & s1
    End If
    If Not IsEmpty(Sheet3.Range("B2")) Then
        s = s & s2
    End If
    If Not IsEmpty(Sheet3.Range("B3")) Then
        s = s & s3
    End If
    s = s & " ORDER BY [ID]"
    'Debug.Print s

    oRst.Open s, oCnn, adOpenStatic, adLockOptimistic, adCmdText

    Sheet3.Range("A7:D11000").ClearContents    'Xoa vung du lieu
    Sheet3.Range("A7").CopyFromRecordset oRst   'Do du lieu tu Recordset da loc xuong sheet
    Sheet3.Range("A5") = "Tong so record tim thay: " & oRst.RecordCount
    MsgBox Timer - thoigian

LocRst_Exit:
    oRst.Close
    Set oRst = Nothing
    If Not oCnn Is Nothing Then
        oCnn.Close
        Set oCnn = Nothing
    End If
    Exit Sub

LocRst_Err:
    Select Case Err.Number
    Case -2147217900
        MsgBox "Sai dieu kien loc du lieu!" & vbNewLine & vbNewLine & Err.Description, vbCritical, "Thông báo"
        Exit Sub
    Case Else
        MsgBox "Loi: " & Err.Number & vbNewLine & "Noi dung loi: " & Err.Description, vbExclamation, "Thông báo"
        Exit Sub
End Select

End Sub

Cách của bạn tôi đã có đề cập trên diễn đàn này rồi. Như đề bài này thì tôi mở ra là dùng những phương pháp mà mình không mở kết nối đó bạn.

Tốc độ hàm của em chắc cũng không thua đâu

Bạn hãy thử với 1 triệu dòng xem sao nhé.

 

[thuyyeu99]

Vì chưa thấy có trên GPE nên em muốn giới thiệu cho anh em phát triển thêm đó anh. Càng chi tiết càng dễ hình dung, sau này anh em quen và hiểu rõ rồi thì tùy biến mà dùng anh.

Cách của bạn tôi đã có đề cập trên diễn đàn này rồi. Như đề bài này thì tôi mở ra là dùng những phương pháp mà mình không mở kết nối đó bạn.


Bạn hãy thử với 1 triệu dòng xem sao nhé.

Anh co cai file gởi lên di, em test thử với

 

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

Anh co cai file gởi lên di, em test thử với

Dữ liệu thì dễ rồi, bạn tự giả lập trên file đó giúp mình nhé.

 

[thuyyeu99]

Hichic em đang tạo 1.000.000 row test không mà máy còn chạy không muốn nổi sao mà Test đây

 

[thuyyeu99]

Dữ liệu thì dễ rồi, bạn tự giả lập trên file đó giúp mình nhé.

Hình như em thắng Tốc độ của anh chắc luôn rồi

 

[Kiều Mạnh]

Hình như em thắng Tốc độ của anh chắc luôn rồi

Code trên Delphi à ???
Mạnh cảm giác thấy cũng code như nhau mà khi code trong Delphi thấy nó chạy có vẻ nhanh hơn hay sao ý ?

nhất là mấy Hàm khai báo sự kiện Viết COM cho Excel (từ ngữ dùng có thể sai ai đó thấy sai sửa lại cho chuẩn he ???!!!)

 

[thuyyeu99]

Code trên Delphi à ???
Mạnh cảm giác thấy cũng code như nhau mà khi code trong Delphi thấy nó chạy có vẻ nhanh hơn hay sao ý ?

nhất là mấy Hàm khai báo sự kiện Viết COM cho Excel (từ ngữ dùng có thể sai ai đó thấy sai sửa lại cho chuẩn he ???!!!)

Em cũng không rành vụ này. Code này trên delphi, còn có thể nâng cấp tốc độ lên nữa nhưng có thể ngoài kiến thức của em....

 

[ongke0711]

Bạn hãy thử với 1 triệu dòng xem sao nhé.

Khi thử với 1 triệu dòng thì tôi mới biết là cái Name Range khai báo với số dòng không được vượt quá 65.536 dòng. Khi tôi khai báo Name: =Sheet1!$A$1:$D$1000000 là Excel báo lỗi không tìm thấy name range ngay. Sau đó tôi phải đổi lại là: =Sheet1!$A:$D thì mới hết báo lỗi.

Hình như em thắng Tốc độ của anh chắc luôn rồi

Cái hàm của bạn xử lý nhanh thật. Cái cách tiếp cận mà Delphi dùng để thao tác, xử lý dữ liệu này là gì vậy bạn?

 

[Nguyễn Duy Tuân]

Cái hàm của bạn xử lý nhanh thật. Cái cách tiếp cận mà Delphi dùng để thao tác, xử lý dữ liệu này là gì vậy bạn?

Bạn theo dõi chuỗi bài biết của tôi về Delphi trên GPE và trên Youtube bạn sẽ. Học đc mức cơ bản.

Bộ bài giảng học lập trình Delphi cơ bản

Hướng dẫn lập trình Delphi cơ bản từ Bluesofts.net

Delphi hiện là một trong các ngôn ngữ lập trình mạnh trên thế giới, cho phép lập trình duy nhất một code và biên dịch cho các nền tảng hệ điều hành Windows (32, 64-bit), Mac OSX, iPhone, Android, Linux. Lập trình trên Delphi rất trực quan, các component phong phú, tương tác linh hoạt với hệ điều...

www.giaiphapexcel.com

 

[thuyyeu99]

Khi thử với 1 triệu dòng thì tôi mới biết là cái Name Range khai báo với số dòng không được vượt quá 65.536 dòng. Khi tôi khai báo Name: =Sheet1!$A$1:$D$1000000 là Excel báo lỗi không tìm thấy name range ngay. Sau đó tôi phải đổi lại là: =Sheet1!$A:$D thì mới hết báo lỗi.




Cái hàm của bạn xử lý nhanh thật. Cái cách tiếp cận mà Delphi dùng để thao tác, xử lý dữ liệu này là gì vậy bạn?

Ngoai ra ban cũng vào pos lập trình dll với excel cua anh tuân, va doc các bài phân tích của anh Vietmimi va anh Batman1 có nhiều cái hay để nghiên cứu lắm hihih

 

[Kiều Mạnh]

Khi thử với 1 triệu dòng thì tôi mới biết là cái Name Range khai báo với số dòng không được vượt quá 65.536 dòng. Khi tôi khai báo Name: =Sheet1!$A$1:$D$1000000 là Excel báo lỗi không tìm thấy name range ngay. Sau đó tôi phải đổi lại là: =Sheet1!$A:$D thì mới hết báo lỗi.




Cái hàm của bạn xử lý nhanh thật. Cái cách tiếp cận mà Delphi dùng để thao tác, xử lý dữ liệu này là gì vậy bạn?

Trước đây Mạnh cũng đã từng tự học Delphi nhưng chỉ biết mấy cái lèo tèo thôi ... ???
https://www.giaiphapexcel.com/diendan/threads/tạo-và-sử-dụng-thư-viện-liên-kết-Động-dll-windows-api.125604/

Sau khi có thớt sau thì Mạnh mới thật sự biết nhiều về Delphi .... Giờ tự viết Hàm API ( DLL ) tạm xài ???

1/ Vô link sau tham khảo coi .... Mạnh học từ đó đấy và bắt đầu thật sự biết viết Hàm API từ đó
Trong thớt đó nếu Bạn thật sự đam mê là học được .... Có nhiều bài viết rất hay mà tìm trên mạng hình như ko có đấy ???

2/ Có mấy bài viết của @Nguyễn Duy Tuân Và Anh @batman1 mới đọc thấy ko có chi nhưng sau này nghiền Delphi rồi thì thấy kinh nghiệm viết ra mấy dòng ấy phải trên 10 Năm ???

3/ Nghiền ngẫm mấy bài về mảng của @thuyyeu99 .... mấy bài đó là xài nhiều nhất trong bất cứ cái dự án code nào hầu như code nào cũng xài mảng ... Nó gắm liền với cuộc đời và sự nghiệp của một tay Coder !!!??? khi rành mảng 1D, 2D ... thì Bạn xử lý đầy các vấn đề ra đó

https://www.giaiphapexcel.com/diendan/threads/ai-muốn-lập-trình-dll-cho-excel-và-các-loại-bằng-delphi-thì-xem-video-này-nhé.137281/

 

[thichproc]

Sắp xếp và lọc duy nhất:

...

Em sắp xếp dữ liệu để xóa trùng Mã Khách hàng nhưng bị lỗi này. Lẽ nào đối tượng Recordset của em không hỗ trợ Sort và Filter?
Bác chỉ em cách sửa với!

 

[ongke0711]

Em sắp xếp dữ liệu để xóa trùng Mã Khách hàng nhưng bị lỗi này. Lẽ nào đối tượng Recordset của em không hỗ trợ Sort và Filter?
Bác chỉ em cách sửa với!

Bạn phải thêm: rs.CurrentLocation = adUseClient (nếu khai báo muộn thì = 3)
Sort property nó yêu cầu CurrentLocation phải là adUseClient

 

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

Em sắp xếp dữ liệu để xóa trùng Mã Khách hàng nhưng bị lỗi này. Lẽ nào đối tượng Recordset của em không hỗ trợ Sort và Filter?
Bác chỉ em cách sửa với!

View attachment 214974

Đối với câu lệnh trên, bạn dùng select distinct cho lọc duy nhất và sắp xếp = order by (Bạn đã làm) là được. Lưu ý là bài mà tôi giới thiệu khác với truy vấn dữ liệu bằng cách kết nối với CSDL nhé bạn.

 

[thichproc]

Bạn phải thêm: rs.CurrentLocation = adUseClient (nếu khai báo muộn thì = 3)
Sort property nó yêu cầu CurrentLocation phải là adUseClient

Cảm ơn bác. Nhờ bác em đã giải quyết vấn đề sort và filter.
Em lại gặp vấn đề khác là khi em thêm 1 field "STT", rồi em duyệt rs, gán rs.Fields("STT").value = rs.AbsolutePosition
Nhưng lệnh trên lại bị lỗi. Bác giúp em thêm với!

Bài đã được tự động gộp: 10/4/19

Đối với câu lệnh trên, bạn dùng select distinct cho lọc duy nhất và sắp xếp = order by (Bạn đã làm) là được. Lưu ý là bài mà tôi giới thiệu khác với truy vấn dữ liệu bằng cách kết nối với CSDL nhé bạn.

Thanks Bác HLMT.
Em muốn xóa trùng trong trường hợp Khách hàng có cùng tổng số dư nhưng khác PGD. Chưa nghĩ ra câu lệnh SQL nào làm được nên em phải sử dụng duyệt recordset để xóa.
PS: Bác thông cảm vì em đã làm hỏng luồng của bác. Hic.

 

[nmhung49]

Cũng nhân đây nmhung49 cũng góp thêm các khác với dữ liệu 1tr dòng thì lấy data chưa đầy 5s

 

[VetMini]

@Bài #46:
Muốn xoá trùng thì dùng:
SELECT TrườngChính, Max(Trường 1), Max(Trường 2), ...
From ...
GROUP BY TrườngChính

 

[thichproc]

@Bài #46:
Muốn xoá trùng thì dùng:
SELECT TrườngChính, Max(Trường 1), Max(Trường 2), ...
From ...
GROUP BY TrườngChính

Dữ liệu em thế này:
MaKH TenKH Tong SD PGD
1234. Nguyen Van A. 100000. 01.
1234. Nguyen Van A. 100000. 02.
2345. Nguyen Van B. 200000. 00.
2345. Nguyen Van B. 200000. 01.

Sau khi xóa trùng, cho ra kết quả:
MaKH TenKH Tong SD PGD
1234. Nguyen Van A. 100000. 01.
2345. Nguyen Van B. 200000. 00.

Em chưa nghĩ ra câu lệnh SQL.

 

[VetMini]

Select MaKH, TenKH, [Tong SD], Min(PGD)
From
...
Group By MaKH, TenKH, [Tong SD]
Order By MaKH, TenKH, [Tong SD]

 

[thichproc]

Select MaKH, TenKH, [Tong SD], Min(PGD)
From
...
Group By MaKH, TenKH, [Tong SD]
Order By MaKH, TenKH, [Tong SD]

Chuẩn rồi bác ơi! Vậy mà em nghĩ hoài ko ra. Thanks bác.
Còn bài #46, phần đầu, về việc gán STT, bác giúp em với!

 

[ongke0711]

Select MaKH, TenKH, [Tong SD], Min(PGD)
From
...
Group By MaKH, TenKH, [Tong SD]
Order By MaKH, TenKH, [Tong SD]

Câu lệnh này của anh Vietmini đúng ra là: lọc danh sách duy nhất. Kiểu như Select DistinctRow...
Vì nếu có 1 MaKH khác xuất hiện 1 dòng thì nó cũng sẽ liệt kê ra luôn.

Câu lệnh SQL của em sẽ là: theo điều kiện trùng [MaKH], trùng [TongSD], khác [PGD]

SELECT Table1.MaKH, Table1.TenKH, Table1.TongSD,Table1.PGD, (SELECT Count(*)
     FROM Table1 As X
     WHERE X.MaKH = Table1.MaKH
         And X.TongSD=Table1.TongSD
         And X.PGD <= Table1.PGD) AS SeqNo, IIf([SeqNo] >1,"Trung","") AS Status
FROM Table1;

- Cái trường phụ: SeqNo chỉ để đánh dấu số thứ tự nếu có dòng trùng các điều kiện trên xuất hiện, xuất hiện mấy dòng sẽ đánh số tăng dần theo 2, 3 ,4... Sau đó trường [Status] dùng để hiện text cho dễ hiểu dòng trùng nếu [SegNo>1).

 

[ongke0711]

Em lại gặp vấn đề khác là khi em thêm 1 field "STT", rồi em duyệt rs, gán rs.Fields("STT").value = rs.AbsolutePosition
Nhưng lệnh trên lại bị lỗi. Bác giúp em thêm với!

- Cái AbsolutePosition của ADO recordset nó chỉ là tương đối tuỳ theo câu lệnh sql, trường chọn sắp xếp (sort) của bạn thôi, bạn gán vô CSDL làm gì.
- Nếu chỉ muốn hiển thị lên Form cho người dùng dễ nhìn thì lúc đó lồng thêm câu lệnh Sql để lấy số thứ tự xong rồi thôi, khỏi ghi lên database cho phí.

 

[thuyyeu99]

Dữ liệu em thế này:
MaKH TenKH Tong SD PGD
1234. Nguyen Van A. 100000. 01.
1234. Nguyen Van A. 100000. 02.
2345. Nguyen Van B. 200000. 00.
2345. Nguyen Van B. 200000. 01.

Sau khi xóa trùng, cho ra kết quả:
MaKH TenKH Tong SD PGD
1234. Nguyen Van A. 100000. 01.
2345. Nguyen Van B. 200000. 00.

Em chưa nghĩ ra câu lệnh SQL.

Do không biết dữ liệu chính xác của bạn làm sao, nên đón mò Ta có thể kết hợp Group và Having là ra thôi

 

[Huyyeu99999]

Cái bài này nghiệm ra có nhiều cái hay thật, nó có cách không dùng vòng lập đưa thẳng vào Recoset đó anh @Hai Lúa Miền Tây

 

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

Cái bài này nghiệm ra có nhiều cái hay thật, nó có cách không dùng vòng lập đưa thẳng vào Recoset đó anh @Hai Lúa Miền Tây

Chia sẻ đi bạn.

 

[Huyyeu99999]

Hihi đơn giản lắm anh đừng nghĩ cao quá, bản thân excel nó có đó anh

 

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

Hihi đơn giản lắm anh đừng nghĩ cao quá, bản thân excel nó có đó anh

Nếu biết cách nào hay hơn thì bạn cứ chia sẻ để mọi người cùng học tập. Có thể đối với bạn nó đơn giản, nhưng chưa chắc đối với ngưới khác giống bạn.

 

[Huyyeu99999]

Nếu biết cách nào hay hơn thì bạn cứ chia sẻ để mọi người cùng học tập. Có thể đối với bạn nó đơn giản, nhưng chưa chắc đối với ngưới khác giống bạn.

Chìa khóa XlRangeValueDataType
xlRangeValueXMLS Lansheet

Bài đã được tự động gộp: 16/1/20

Sub Copy()
Dim xml As string
xml = Application.ActiveSheet.Range("A1:D20").Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet)
Application.ActiveSheet.Range("G1:j20").Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet) = xml
End Sub

Có xml này anh đưa vào record

 

[HeSanbi]

Thông thường ta truy vấn dữ liệu bằng ADO trên cùng file thì ta vẫn phải mở kết nối đến chính file thực thi đó. Điều này làm giảm đi tốc độ khi chạy code.
Hôm nay mình xin chia sẻ với các bạn cách truy vấn dữ liệu mà không cần mở kết nối trên cùng file thực thi.

Sub LocDL_HLMT()
    Dim rst As Object, vArray As Variant, i As Long, thoigian As Long 'Khai bao bien
    Set rst = CreateObject("ADODB.Recordset") ' Khoi tao Recordset
    vArray = Sheet1.Range("A2:D10001").Value ' Du lieu nguon vao mang
    thoigian = Timer()
    With rst
        'Khai bao va mo Recorset
        .Fields.Append "ID", 3
        .Fields.Append "MatID", 200, 10
        .Fields.Append "Balance", 3
        .Open
        'Dua du lieu nguon tu mang da set vao Recordset
        For i = LBound(vArray) To UBound(vArray)
            .AddNew
            .Fields("ID").Value = vArray(i, LBound(vArray))
            .Fields("MatID").Value = vArray(i, LBound(vArray) + 1)
            .Fields("Balance").Value = vArray(i, LBound(vArray) + 3)
            .Update
        Next
        'Loc du lieu trong Recordset
        .Filter = "Balance " & Sheet2.Range("B1")
    End With
    Sheet2.Range("A2:D11000").ClearContents 'Xoa vung du lieu
    Sheet2.Range("A4").CopyFromRecordset rst 'Do du lieu tu Recordset da loc xuong sheet
    MsgBox Timer - thoigian
    rst.Close
    Set rst = Nothing
End Sub

Bài viết của anh @Hai Lúa Miền Tây hoàn toàn có vấn đề. Anh đọc qua bài viết dưới đây để hiểu vì sao như vậy.


----------------------
Không biết mắt có đọc nhằm không. Đọc 2-3 lần
Đọc bài viết và đọc code là "MatID", khi vào đọc trong Data thì là "MaID"


Sao anh @Hai Lúa Miền Tây lại để LBound của Hàng sang Cột được, hơi khó hiểu.

Đoạn code phải là:

     Dim LB%, LB2%
     LB= LBound(vArray): LB2 = LBound(vArray, 2)
        .Fields.Append vArray(LB, LB2), 3
        .Fields.Append vArray(LB, LB2 + 1), 200, 10
        .Fields.Append vArray(LB, LB2 + 3), 3
     '......

     .Fields(vArray(LB, LB2)).Value = vArray(i, LB2)
     .Fields(vArray(LB, LB2 + 1)).Value = vArray(i, LB2 + 1)
     .Fields(vArray(LB, LB2 + 3)).Value = vArray(i, LB2 + 3)

----------------------

Đến bài #47: cũng là Code "MatID" và trong Data thì là "MaID".

Cũng nhân đây nmhung49 cũng góp thêm các khác với dữ liệu 1tr dòng thì lấy data chưa đầy 5s

----------------------


Và không hiểu vì sao anh không Thực hiện truy vấn dữ liệu file đang mở vào Recordset bằng cách dưới đây. Mà phải thông qua vòng lặp (vô lý kia).
Tham số Data Source, ADO Sẽ thực hiện tìm xem File có được mở cùng một Process hay không, có thì truy vấn trực tiếp không thì phải mở File sau đó thực hiện kết nối. Ở đây ADO được khởi tạo cùng tiến trình với VBA và cùng tiến trình với Excel hiện tại. Nên File sẽ được kết nối ngay lập tức.

*Hiểu về Process: là Tiến trình chính - tiến trình Excel Application đang hoạt động trong Bộ nhớ máy tính.
----------------------

Sub testRecordset()
  Dim T#: T = Timer
  'Lưu ý: File phải được Save lại với định dạng chứa Macro để phù hợp đối số "Excel 12.0 Xml" mới test được Code này'
  With CreateObject("ADODB.Recordset")
    .Open "SELECT * FROM `" & ThisWorkbook.Sheets(1).Name & "$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml"""
    .Close
  End With
  Debug.Print Round(Timer - T, 5)
End Sub

----------------------------
Phương pháp trên của anh thực ra chỉ nhờ vào Tốc độ xử lý và thuật toán trong ADODB, và nó được viết bằng C++, chứ không phải một phương pháp để cải thiện tốc độ khi truy vấn một File Excel đang mở.

Đúng hơn thì không phải là phương pháp.

Bài viết của anh đã vô tình làm người đọc hiểu nhầm là ADO luôn phải mở mới truy vấn kể cả File cùng một Process.

 

[HeSanbi]

Chìa khóa XlRangeValueDataType
xlRangeValueXMLS Lansheet

Bài đã được tự động gộp: 16/1/20

Sub Copy()
Dim xml As string
xml = Application.ActiveSheet.Range("A1:D20").Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet)
Application.ActiveSheet.Range("G1:j20").Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet) = xml
End Sub

Có xml này anh đưa vào record

----------------------
Bạn chớ nói Vui mà không thực hành.

Phương thức chuyển Giá trị Range thành XML (xlRangeValueXMLSpreadsheet) là cả một vấn đề. Thì làm sao nói đến được đưa vào Recordset ADODB để xử lý.
Và làm sao cải thiện được tốc độ xử lý dữ liệu.
Xử lý sang XML với 1 triệu dòng là đã "toi Excel" rồi.

Bài viết này đang đề cập đến truy vấn dữ liệu. Chính xác hơn là tận dụng tối đa thư viện ADODB.

Với cách bạn nói là Chuyển đổi vùng lưu trữ dữ liệu thành dạng lưu trữ mới phù hợp với Recordset ADODB có thể hiểu được và xử lý được.

Khác nào bạn đang truy vấn một file dữ liệu dạng XML, nhưng ở đây phải bỏ thêm bước chuyển đổi.

 

[Huyyeu99999]

----------------------
Bạn chớ nói Vui mà không thực hành.

Phương thức chuyển Giá trị Range thành XML (xlRangeValueXMLSpreadsheet) là cả một vấn đề. Thì làm sao nói đến được đưa vào Recordset ADODB để xử lý.
Và làm sao cải thiện được tốc độ xử lý dữ liệu.
Xử lý sang XML với 1 triệu dòng là đã "toi Excel" rồi.

Bài viết này đang đề cập đến truy vấn dữ liệu. Chính xác hơn là tận dụng tối đa thư viện ADODB.

Với cách bạn nói là Chuyển đổi vùng lưu trữ dữ liệu thành dạng lưu trữ mới phù hợp với Recordset ADODB có thể hiểu được và xử lý được.

mình chưa nói tới so sánh tốc độ, bài này là nói không mở kết nối mà, đúng như yêu cầu của bài chứ đâu có lệch đâu. mình chưa thử 1.000.000 nên không biết mà chắc chắn là nó chậm hơn lấy vào Arr rồi. Máy chạy hông nổi

Bài đã được tự động gộp: 17/1/20

----------------------
Bạn chớ nói Vui mà không thực hành.

Phương thức chuyển Giá trị Range thành XML (xlRangeValueXMLSpreadsheet) là cả một vấn đề. Thì làm sao nói đến được đưa vào Recordset ADODB để xử lý.
Và làm sao cải thiện được tốc độ xử lý dữ liệu.
Xử lý sang XML với 1 triệu dòng là đã "toi Excel" rồi.

Bài viết này đang đề cập đến truy vấn dữ liệu. Chính xác hơn là tận dụng tối đa thư viện ADODB.

Với cách bạn nói là Chuyển đổi vùng lưu trữ dữ liệu thành dạng lưu trữ mới phù hợp với Recordset ADODB có thể hiểu được và xử lý được.

Khác nào bạn đang truy vấn một file dữ liệu dạng XML, nhưng ở đây phải bỏ thêm bước chuyển đổi.

theo như bạn có cách nào hay không vẫn giữ định dạng của sheet

 

[HeSanbi]

mình chưa nói tới so sánh tốc độ, bài này là nói không mở kết nối mà, đúng như yêu cầu của bài chứ đâu có lệch đâu. mình chưa thử 1.000.000 nên không biết

Chắc bạn hiểu không hết đoạn "truy vấn dữ liệu", nghĩa của nó đã bao hàm tốc độ rồi bạn.
"ADODB...." Thư viện này cũng nói lên tốc độ xử lý dữ liệu.

Có thể hiểu như thế này:
Excel đã sử dụng ngôn ngữ đánh dấu XML hiển thị thành bảng -> ADODB có thể truy vấn ngay lập tức.

Với cách làm của bạn:
Excel sử dụng ngôn ngữ đánh dấu XML -> Chuyển mảng sang XML (Phí bước này) -> ADODB Recordset

Bài đã được tự động gộp: 17/1/20

theo như bạn có cách nào hay không vẫn giữ định dạng của sheet

Đọc lại bài trước #60 của tôi bạn sẽ thấy cách vận dụng.

 

[Huyyeu99999]

Chắc bạn hiểu không hết đoạn "truy vấn dữ liệu", nghĩa của nó đã bao hàm tốc độ rồi bạn.
"ADODB...." Thư viện này cũng nói lên tốc độ xử lý dữ liệu.

Có thể hiểu như thế này:
Excel đã sử dụng ngôn ngữ đánh dấu XML hiển thị thành bảng -> ADODB có thể truy vấn ngay lập tức.

Với cách làm của bạn:
Excel sử dụng ngôn ngữ đánh dấu XML -> Chuyển sang XML (Phí bước này) -> ADODB Recordset

ý của mình là muốn giữ nguyên định dạng của sheet kìa chứ không phải là chỉ đưa giá trị vào Recordset. vậy từ excel có cách nào đọc trực tiếp xml không, phai nguyên định dạng format, mau chu, công thức.....?

 

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

Bài viết của anh @Hai Lúa Miền Tây hoàn toàn có vấn đề. Anh đọc qua bài viết dưới đây để hiểu vì sao như vậy.


----------------------
Không biết mắt có đọc nhằm không. Đọc 2-3 lần
Đọc bài viết và đọc code là "MatID", khi vào đọc trong Data thì là "MaID"

Đó là ý đồ của tôi, cái cột tiêu đề trong sheet 1 không dính gì đến code, vùng dữ liệu tôi đưa vào cũng không lấy dòng tiêu đề đó. Bạn có thể tạo cột tiêu đề theo ý trong bảng, tên cột không nhất thiết phải giống tên cột dữ liệu nguồn (MatID và MaID) và ta có thể bỏ bớt cột dữ liệu không cần thiết (Remark), cũng có thể chọn thứ tự cột theo ý. Sau khi tạo xong bảng rỗng dữ liệu sau đó ta đưa dữ liệu vào.

Sao anh @Hai Lúa Miền Tây lại để LBound của Hàng sang Cột được, hơi khó hiểu.

Nhưng kết quả bạn thấy như thế nào?

 

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

ý của mình là muốn giữ nguyên định dạng của sheet kìa chứ không phải là chỉ đưa giá trị vào Recordset. vậy từ excel có cách nào đọc trực tiếp xml không, phai nguyên định dạng format, mau chu, công thức.....?

Xử lý cách này chỉ với dữ liệu nhỏ, riêng lẻ thôi bạn. Còn xử lý theo cách của tôi có thể dùng với dữ liệu lớn, kết quả cho ra không giữ lại định dạng hay công thức.

 

[Huyyeu99999]

 

[HeSanbi]

ý của mình là muốn giữ nguyên định dạng của sheet kìa chứ không phải là chỉ đưa giá trị vào Recordset. vậy từ excel có cách nào đọc trực tiếp xml không, phai nguyên định dạng format, mau chu, công thức.....?

Copy file sang file mới bung file thành một folder bằng Zip7z, sau đó truy cập file XML trong thư mục worksheets, File XML chứa toàn bộ định dạng + công thức + giá trị, tệp sẽ rất nặng (gấp 10 đến vài chục lần) khiến máy tính xử lý rất nhọc nhằn. Nếu bạn biết thư viện nào hỗ trợ XML tốt thì có thể Vận dụng. Với VBA thì không thể xử lý dữ liệu lớn được.


--------------------------------------
@Hai Lúa Miền Tây
Anh có vẻ vui tính đây. LBound(Arr) và LBound(Arr, 2) của mảng khai báo kia luôn luôn là 1, chắc chắn là không sai rồi, nhưng ai lại viết như vậy.


Và vẫn chưa hiểu nổi vì sao anh lại cho rằng sử dụng vòng lặp sẽ nhanh hơn truy vấn trực tiếp. Quá đổi rườm rà và chậm.

Cách dùng vòng lặp Trừ khi ta cần phải xử lý dữ liệu mảng trước khi đưa vào Recordset.
Hoặc là Mảng thuần trong VBA nên cần sử dụng vòng lặp.

Câu nói: "truy vấn dữ liệu bằng ADO trên cùng file thì ta vẫn phải mở kết nối đến chính file thực thi đó"
Sai hoàn toàn. (Đoạn này đã được nói ở #60)

Dễ gây hiểu nhầm cho người học và vận dụng ADODB.


Code dưới đây phản biện cho câu trên và câu "Điều này làm giảm đi tốc độ khi chạy code".

Driver Microsoft....OLEDB... được sinh ra để tối ưu kết nối cơ sở dữ liệu sử dụng cho Office. Ở trường hợp này đã dùng Vòng lặp thì Tốc độ chắc chắn sẽ chậm hơn truy vấn trực tiếp.

-----

Sub testRecordset()
  Dim T#: T = Timer
  Static RCT As Object
  If RCT Is Nothing Then Set RCT = VBA.CreateObject("ADODB.Recordset")
  RCT.Open "SELECT ID,MaID,Balance FROM [" & ThisWorkbook.Sheets(1).Name & "$] WHERE Balance>0", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;"""
  With ThisWorkbook.Sheets(2)
    .Range("F4:D100000").ClearContents
    .Range("F4").CopyFromRecordset RCT
    RCT.Close
  End With
  Debug.Print Round(Timer - T, 5)
End Sub

------

Và trong code của anh có ăn gian một lượng thời gian ở đoạn này:

------

vArray = Sheet1.Range("A2:D10430").Value
thoigian = Timer()

------
Phải như thế này mới phải:
------

thoigian = Timer()
vArray = Sheet1.Range("A2:D10430").Value

------
Đoạn này thay cho mở kết nối như anh đã nói, sao lại không tính thời gian, nó cũng được tính là truy vấn dữ liệu.

 

[Huyyeu99999]

Copy file sang file mới bung file thành một folder bằng Zip7z, sau đó truy cập file XML trong thư mục worksheets, File XML chứa toàn bộ định dạng + công thức + giá trị, tệp sẽ rất nặng (gấp 10 đến vài chục lần) khiến máy tính xử lý rất nhọc nhằn. Nếu bạn biết thư viện nào hỗ trợ XML tốt thì có thể Vận dụng. Với VBA thì không thể xử lý dữ liệu lớn được.

Cảm ơn ban góp ý.
Mình tính lấy xml chuyển qua client để đưa vào sheet ben client.
Khi cell nào ben client thay đổi mình sẽ chuyển ngược về lại sever. Không biết có khả thi không

 

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

Anh có vẻ vui tính đây. LBound(Arr) và LBound(Arr, 2) của mảng khai báo kia luôn luôn là 1, chắc chắn là không sai rồi, nhưng ai lại viết như vậy.

Tôi viết rõ ràng là LBound(vArray) To UBound(vArray) chứ không phải như bạn nói ở trên.
LBound(vArray) sẽ là 1 và UBound(vArray) là mấy bạn test lại nhé. Còn việc tôi đưa mảng vào Recordset chính là 1 cách để mọi người có thể tùy biến mà dùng.
Cách của bạn, bạn nghĩ là không kết nối đến file nguồn?

 

[HeSanbi]

Tôi viết rõ ràng là LBound(vArray) To UBound(vArray) chứ không phải như bạn nói ở trên.
LBound(vArray) sẽ là 1 và UBound(vArray) là mấy bạn test lại nhé. Còn việc tôi đưa mảng vào Recordset chính là 1 cách để mọi người có thể tùy biến mà dùng.
Cách của bạn, bạn nghĩ là không kết nối đến file nguồn?

-------
Rõ là anh không đọc bài viết thì phải bắt đầu từ #60:

Trong code của anh: vArray(i, LBound(vArray)) Và vArray(i, LBound(vArray, 2)) cách viết đúng?

Có ai nói đến đoạn này sai bao giờ: LBound(vArray) To UBound(vArray)

"Cách của bạn, bạn nghĩ là không kết nối đến file nguồn?"

Hài chết đi được, em đã đăng hai bài rồi, có lẽ anh chưa đọc qua.
File Excel đang mở là file thực hiện truy cập chính nó, vì Driver MS OLEDB được mở từ ADODB trên cùng một Process nên nó không cần truy cập hay mở ở đâu nữa, Mà vào ngay dữ liệu đang mở là File chính truy vấn trực tiếp.

Nó đã cùng một tiến trình rồi, thì anh nghĩ nó sẽ mở File ở đâu nữa đây.

"Cùng một Process" có nói ở #60.

Chắc là tham gia nhằm bài viết.

Bài đã được tự động gộp: 17/1/20

Đoạn này trích từ Trang Microsoft:

"Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls. "
"Hiệu suất . Microsoft Excel là một máy chủ ActiveX out-of-process. ADO chạy trong tiến trình và tiết kiệm chi phí cho các cuộc gọi ngoài quy trình tốn kém. "

https://support.microsoft.com/en-us...-to-use-ado-to-read-and-write-data-in-excel-w

 

[ngovantruong07061994@gmai]

Các tiền bối cho em hỏi chút là nếu tổng hợp dữ liệu từ nhiều file sang 1 file ( coppy dữ liệu thôi không phải chuyển hẳn sheet ) thì sử dụng câu lệnh nào ạ

Ví dụ file 1 có 3 sheet a b c
File 2 có 3 sheet a b1 c1
Giờ em muốn tổng hợp file 1 và file 2 và chỉ tổng hợp 1 sheet a thôi thì làm thế nào ạ, Mong nhận sự chỉ giáo ạ

 

[shnhatha221108]

Cũng xin góp vui với đề tài này.
Tôi demo một cách dùng khác của Recordset nhưng có tạo Connection.
Theo cách của anh HLMT là tạo một table ảo (ADO recordset) trên bộ nhớ và xử lý Filter, Sort. Cách này theo tôi có một điểm yếu đó là nó sẽ tải toàn bộ dữ liệu lên Recordset (10.000 dòng trong ví dụ này). Điều này sẽ ảnh hưởng đến bộ nhớ rõ rệt nếu số lượng record lên hàng triệu.
Cách của tôi đang làm thì do không rành về mảng nên chỉ thuần tuý dùng Recordset và name range của Excel để gán dữ liệu vào recordset. Dữ liệu được chọn lọc trước khi ghi lên Recordset nên về mặt bộ nhớ sẽ giảm áp lực hơn.
Các bạn tham khảo góp ý nhé.

- Cách làm này phải tốn thêm một bước là tạo cái Name range và cập nhật nó mỗi khi có thay đổi thêm số dòng vào dữ liệu.
- Tôi có viết cái hàm để cập nhật lại name range.

Option Explicit

Function createDynamicNamedRange() As Boolean
'--------------------------------------------------------------------------------------
'# Áp dung khi da xac dinh so cot cua range, hàm chi cap nhat dong cho so dòng cua range
'--------------------------------------------------------------------------------------

    Dim sht                     As Worksheet
    Dim lngFirstRowRng          As Long
    Dim lngLastRowRng           As Long
    Dim lngFirstColRng          As Long
    Dim lngLastColRng           As Long
    Dim myDynamicNamedRange     As Range
    Dim sRngName                As String

    Set sht = ThisWorkbook.Worksheets("Sheet1")

    'Khai bao dòng/côt dau tiên cua Range
    lngFirstRowRng = 1
    lngFirstColRng = 1

    'dat ten Name range tuy ý
    sRngName = "TonKhoVT"

    With sht.Cells
        lngLastRowRng = sht.Cells(Rows.Count, "D").End(xlUp).Row
        lngLastColRng = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set myDynamicNamedRange = .Range(.Cells(lngFirstRowRng, lngFirstColRng), .Cells(lngLastRowRng, lngLastColRng))
    End With

    ThisWorkbook.Names.Add Name:=sRngName, RefersTo:=myDynamicNamedRange

End Function

- Code cho Sub lọc dữ liệu:

Sub LocRst()
On Error GoTo LocRst_Err
    Dim oCnn As Object
    Dim oRst As Object
    Dim sRngName As String, fileExcel As String
    Dim s, s1, s2, s3 As String     'Chuoi dieu kien loc
    Dim thoigian As Long

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Set oCnn = CreateObject("ADODB.Connection")
    Set oRst = CreateObject("ADODB.Recordset")

    thoigian = Timer()

    fileExcel = ThisWorkbook.FullName
    With oCnn
        Select Case CLng(Application.Version)
        Case 11   'Excel 2003
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=" & fileExcel & ";" & _
                                "Extended Properties=""Excel 8.0;HDR=Yes;"";"
        Case Is >= 12    'Excel 2007 tro lên"
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & fileExcel & ";" & _
                                "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"";"
        End Select
        .Open
    End With

    sRngName = "TonKhoVT"

    'Tong hop dieu kien loc
    s = "SELECT * FROM [" & sRngName & "] WHERE 1=1"
    s1 = " AND [" & Sheet3.Range("A1") & "]" & Sheet3.Range("B1")                   'Balance
    s2 = " AND [" & Sheet3.Range("A2") & "] =" & Sheet3.Range("B2")                 'ID
    s3 = " AND [" & Sheet3.Range("A3") & "] ='" & Sheet3.Range("B3") & "'"    'Remark
    If Not IsEmpty(Sheet3.Range("B1")) Then
        s = s & s1
    End If
    If Not IsEmpty(Sheet3.Range("B2")) Then
        s = s & s2
    End If
    If Not IsEmpty(Sheet3.Range("B3")) Then
        s = s & s3
    End If
    s = s & " ORDER BY [ID]"
    'Debug.Print s

    oRst.Open s, oCnn, adOpenStatic, adLockOptimistic, adCmdText

    Sheet3.Range("A7:D11000").ClearContents    'Xoa vung du lieu
    Sheet3.Range("A7").CopyFromRecordset oRst   'Do du lieu tu Recordset da loc xuong sheet
    Sheet3.Range("A5") = "Tong so record tim thay: " & oRst.RecordCount
    MsgBox Timer - thoigian

LocRst_Exit:
    oRst.Close
    Set oRst = Nothing
    If Not oCnn Is Nothing Then
        oCnn.Close
        Set oCnn = Nothing
    End If
    Exit Sub

LocRst_Err:
    Select Case Err.Number
    Case -2147217900
        MsgBox "Sai dieu kien loc du lieu!" & vbNewLine & vbNewLine & Err.Description, vbCritical, "Thông báo"
        Exit Sub
    Case Else
        MsgBox "Loi: " & Err.Number & vbNewLine & "Noi dung loi: " & Err.Description, vbExclamation, "Thông báo"
        Exit Sub
End Select

End Sub

Chào anh..!Nhờ anh chỉ bảo giúp với đoạn code trên để đáp dụng cho một file dữ liệu khác thì tại 3 ô giá trị lọc cần thay đổi điều kiện câu lệnh ra sao.?
Vi dụ em muốn tai Sheet3.Range("B1") = một giá trị cụ thể .

 

[ongke0711]

Chào anh..!Nhờ anh chỉ bảo giúp với đoạn code trên để đáp dụng cho một file dữ liệu khác thì tại 3 ô giá trị lọc cần thay đổi điều kiện câu lệnh ra sao.?
Vi dụ em muốn tai Sheet3.Range("B1") = một giá trị cụ thể .

Bạn chỉ cần thay dấu ">" thành dấu "=".
Ở ô B1 này mục đích là lọc giá trị bao gồm luôn các toán tử <, >, = nên tôi phải thiết kế dạng Text và khi nhập liệu phải thêm toán tử vô và nhập kiểu text.
Vd: bạn muốn tìm chính xác giá trị 150 thì phải gõ "'=150"
Nếu trường hợp của bạn không cần lọc các trường hợp <, > thì tìm câu lệnh SQL và sửa như bên dưới, sau đó nhập số không cần dấu "="

s1 = " AND [" & Sheet3.Range("A1") & "]=" & Sheet3.Range("B1")

 

[shnhatha221108]

Bạn chỉ cần thay dấu ">" thành dấu "=".
Ở ô B1 này mục đích là lọc giá trị bao gồm luôn các toán tử <, >, = nên tôi phải thiết kế dạng Text và khi nhập liệu phải thêm toán tử vô và nhập kiểu text.
Vd: bạn muốn tìm chính xác giá trị 150 thì phải gõ "'=150"
Nếu trường hợp của bạn không cần lọc các trường hợp <, > thì tìm câu lệnh SQL và sửa như bên dưới, sau đó nhập số không cần dấu "="



Gửi anh..Nếu trong đoạn code " AND [" & Sheet3.Range("A1") & "]" & Sheet3.Range("B1") để nguyên bản như của anh thì có thể sử dụng được toán tử so sánh < hoặc > còn = thì báo lỗi(như file anh em đính kèm).Ngược lại khi em sửa " AND [" & Sheet3.Range("A1") & "]=" & Sheet3.Range("B1") thì chỉ sử dụng được giá trị sác đinh đó ...
Nhân đây em muốn nhờ anh giúp hộ.Nếu ở sheet nguồn em không đặt tên bảng muốn sử dụng tên sheet và vùng dữ liệu và trong câu truy vấn kết nôi (HDR=No) để không phải sử dụng tiêu đề mà sử dung theo kiểu (SELECT f1,f2,f3 FROM ............. where f1 =...).Anh kiểm tra file đính kèm giúp em với..

 

[ongke0711]

Gửi anh..Nếu trong đoạn code " AND [" & Sheet3.Range("A1") & "]" & Sheet3.Range("B1") để nguyên bản như của anh thì có thể sử dụng được toán tử so sánh < hoặc > còn = thì báo lỗi(như file anh em đính kèm).Ngược lại khi em sửa " AND [" & Sheet3.Range("A1") & "]=" & Sheet3.Range("B1") thì chỉ sử dụng được giá trị sác đinh đó ...
Nhân đây em muốn nhờ anh giúp hộ.Nếu ở sheet nguồn em không đặt tên bảng muốn sử dụng tên sheet và vùng dữ liệu và trong câu truy vấn kết nôi (HDR=No) để không phải sử dụng tiêu đề mà sử dung theo kiểu (SELECT f1,f2,f3 FROM ............. where f1 =...).Anh kiểm tra file đính kèm giúp em với..

- Bạn phải học lại cách viết câu lệnh SQL trong VBA. Bạn viết sai cú pháp nên nó lỗi thôi. Phải chính xác từng dấu nháy đơn, nháy kép, "&", khoảng trắng...
- Bạn tham chiếu sai các Cell lấy dữ liệu.



>> phải tương ứng địa chỉ Cell bạn đã thiết kế:




Về việc dùng "HDR=No", tôi khuyên nếu bảng dữ liệu của bạn có "tiêu đề" cột là tiếng Anh hoặc tiếng Việt không dấu thì nên dùng luôn tiêu đề cột cho nó tường minh. Sau này nếu bảng dữ liệu của bạn có bị thay đổi vị trí các cột cũng không cần sửa lại code F1, F2... vì nó tham chiếu thẳng tiêu đề cột, bất kể nó nằm vị trí 1, 2, 3 v.v..
Theo câu lệnh SQL bạn đang viết theo kiểu " Select f1,f2, f3...Where..." thì nếu một trong các tham số điều kiện mà để trống thì code sẽ báo lỗi ngay.

 

[thnghiachau]

- Bạn phải học lại cách viết câu lệnh SQL trong VBA. Bạn viết sai cú pháp nên nó lỗi thôi. Phải chính xác từng dấu nháy đơn, nháy kép, "&", khoảng trắng...
- Bạn tham chiếu sai các Cell lấy dữ liệu.

View attachment 247216

>> phải tương ứng địa chỉ Cell bạn đã thiết kế:

View attachment 247217


Về việc dùng "HDR=No", tôi khuyên nếu bảng dữ liệu của bạn có "tiêu đề" cột là tiếng Anh hoặc tiếng Việt không dấu thì nên dùng luôn tiêu đề cột cho nó tường minh. Sau này nếu bảng dữ liệu của bạn có bị thay đổi vị trí các cột cũng không cần sửa lại code F1, F2... vì nó tham chiếu thẳng tiêu đề cột, bất kể nó nằm vị trí 1, 2, 3 v.v..
Theo câu lệnh SQL bạn đang viết theo kiểu " Select f1,f2, f3...Where..." thì nếu một trong các tham số điều kiện mà để trống thì code sẽ báo lỗi ngay.

Hic cái SQL mình cũng toàn hay sai.... học cách viết rõ như bạn mới được

 

[shnhatha221108]

- Bạn phải học lại cách viết câu lệnh SQL trong VBA. Bạn viết sai cú pháp nên nó lỗi thôi. Phải chính xác từng dấu nháy đơn, nháy kép, "&", khoảng trắng...
- Bạn tham chiếu sai các Cell lấy dữ liệu.

View attachment 247216

>> phải tương ứng địa chỉ Cell bạn đã thiết kế:

View attachment 247217


Về việc dùng "HDR=No", tôi khuyên nếu bảng dữ liệu của bạn có "tiêu đề" cột là tiếng Anh hoặc tiếng Việt không dấu thì nên dùng luôn tiêu đề cột cho nó tường minh. Sau này nếu bảng dữ liệu của bạn có bị thay đổi vị trí các cột cũng không cần sửa lại code F1, F2... vì nó tham chiếu thẳng tiêu đề cột, bất kể nó nằm vị trí 1, 2, 3 v.v..
Theo câu lệnh SQL bạn đang viết theo kiểu " Select f1,f2, f3...Where..." thì nếu một trong các tham số điều kiện mà để trống thì code sẽ báo lỗi ngay.

Vâng .!cảm ơn anh do em chưa đi chuyên sâu về lĩnh vực này nên chỉ dám học mót mỗi vấn đề một chút thôi ạ nên có nhiều sai sót..
Anh cho em hỏi phần khai báo dưới có phải theo kiểu dữ liệu ,nhờ anh giải thích giúp...Còn điều kiện lọc Where 1=1' là sao ạ..?Mong được anh và anh chị em trên diễn đàn chỉ bảo..
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

 

[ongke0711]

Anh cho em hỏi phần khai báo dưới có phải theo kiểu dữ liệu ,nhờ anh giải thích giúp...Còn điều kiện lọc Where 1=1' là sao ạ..?Mong được anh và anh chị em trên diễn đàn chỉ bảo..
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

- Các hằng số (adOpenStatic,...) là tham số cho đối tượng Connection, Recordset của thư viện ADODB. Do trong file viết code kiểu khai báo muộn (Late Binding) thư viện ADODB nên buộc phải khai báo các hằng như trên để VBA nó hiểu, không bắt lỗi chưa khai báo biến. Nếu dùng Early Binding sẽ không cần khai báo các dòng trên. Bạn tự tìm hiểu 2 kiểu khai báo này theo từ khoá đã nêu trên.
- Về câu lệnh SQL tôi thêm "Where 1 = 1" là thủ thuật để tôi nối thêm các điều kiện sau nữa và các điều kiện sau này có thể tuỳ chọn, có cái có, có cái không. Nếu không có thêm các điều kiện phía sau nữa thì câu lệnh SQL vẫn luôn chạy vì 1=1 --> True. Nếu không có "Where 1 =1" thì việc nối thêm chuỗi điều kiện phía sau sẽ dài dòng, phức tạp hơn.
Ví dụ:
SELECT * FROM tblABC

Nếu thêm 2 điều kiện lọc dữ liệu nữa: [NgaySX]=#01/01/2010# và [MaHang] = 'HH001'
2 điều kiện này có thể tìm cả 2, 1 trong 2 hoặc không lọc theo điều kiện nào cả nếu gười dùng không nhập dữ kiện cho nó.
Khi đó câu lênh SQL sẽ là:

"... WHERE [NgaySX] = #" & IIF(Cell 'B2' = Null, "*", Cell 'B2') & "# AND [MaHang] ='" & IIF(Cell 'B3' = Null, "*", Cell 'B3') & "'"

Thường cái Form tìm kiếm nó sẽ bao gồm cả chục điều kiện tìm kiếm, nếu viết kiểu trên thì nó sẽ rất rối, khó sửa nên tôi chọn cách thêm 1=1 cho đơn giản câu lệnh.

 

[shnhatha221108]

- Các hằng số (adOpenStatic,...) là tham số cho đối tượng Connection, Recordset của thư viện ADODB. Do trong file viết code kiểu khai báo muộn (Late Binding) thư viện ADODB nên buộc phải khai báo các hằng như trên để VBA nó hiểu, không bắt lỗi chưa khai báo biến. Nếu dùng Early Binding sẽ không cần khai báo các dòng trên. Bạn tự tìm hiểu 2 kiểu khai báo này theo từ khoá đã nêu trên.
- Về câu lệnh SQL tôi thêm "Where 1 = 1" là thủ thuật để tôi nối thêm các điều kiện sau nữa và các điều kiện sau này có thể tuỳ chọn, có cái có, có cái không. Nếu không có thêm các điều kiện phía sau nữa thì câu lệnh SQL vẫn luôn chạy vì 1=1 --> True. Nếu không có "Where 1 =1" thì việc nối thêm chuỗi điều kiện phía sau sẽ dài dòng, phức tạp hơn.
Ví dụ:
SELECT * FROM tblABC

Nếu thêm 2 điều kiện lọc dữ liệu nữa: [NgaySX]=#01/01/2010# và [MaHang] = 'HH001'
2 điều kiện này có thể tìm cả 2, 1 trong 2 hoặc không lọc theo điều kiện nào cả nếu gười dùng không nhập dữ kiện cho nó.
Khi đó câu lênh SQL sẽ là:

"... WHERE [NgaySX] = #" & IIF(Cell 'B2' = Null, "*", Cell 'B2') & "# AND [MaHang] ='" & IIF(Cell 'B3' = Null, "*", Cell 'B3') & "'"

Thường cái Form tìm kiếm nó sẽ bao gồm cả chục điều kiện tìm kiếm, nếu viết kiểu trên thì nó sẽ rất rối, khó sửa nên tôi chọn cách thêm 1=1 cho đơn giản câu lệnh.

View attachment 247303

Cảm ơn anh đã giải thích rất rõ ràng nhưng với em những điều trên thật đúng là vượt ngoài khả năng hiểu biết.Em sẽ cố tìm hiểu dần để biết thêm phần nào tốt phần đó.Một lần nữa xin cảm ơn anh đã nhiệt tình chỉ bảo..!