Kết nối giữa các file Excel bằng ADODC.

Chia sẻ bởi:hands

★★★★★

‹ ADO: Kết nối Excel Database cho công... Lỗi Khi lấy dữ liệu bằng ADO. ›

Quảng cáo

Mình đã cùng Thu Nghi và Challenge98 thảo luận về vấn đề kết nối và trích rút dữ liệu giữa các File Excel. Có rất nhiều cách, nhưng ADODC cũng là 1 cách khá hay. Nhưng rồi kết quả cũng quên đi.
Trong thực tế có rất nhiều người hỏi về vấn đề này, vậy mình Post lên đây file ví dụ để các bạn tham khảo và mở rộng cho chương trình khi cần thiết. Đây là ví dụ đơn giản nhất có thể nên cũng là dễ hiểu với các bạn mới vọc. Hy vọng các bạn tìm được điều gì đó ở ví dụ này.
(Lưu ý: Khởi bằng ZMain)

www.giaiphapexcel.com/diendan/threads/k%E1%BA%BFt-n%E1%BB%91i-gi%E1%BB%AFa-c%C3%A1c-file-excel-b%E1%BA%B1ng-adodc.24648/

Khóa học SprinGO phù hợp

Học Nhân sự Tổng hợp – Trở thành chiến binh nhân sự vững nghiệp vụ

Con người là một trong những yếu tố quan trọng của công ty, là tài sản quý giá của doanh nghiệp. Chính vì thế,...

Xem khóa học

★★★★★ 5 ★ 1 👤 6 ▥ 0

‹ ADO: Kết nối Excel Database cho công việc... Lỗi Khi lấy dữ liệu bằng ADO. ›

Quảng cáo

Bạn nên đọc

6 Responses

hands says:

01/02/2023 at 7:54 pm

Em chay bị lỗi

Em chạy thì báo lỗi tại dòng
Set cnEx = New ADODB.Connection
2878
Bác giải thích giúp em nhé

Bạn vào tools–Reference–Chọn Microsoft ActiveX Data Ojects 2.0…và Visual Basic for App…
Để khắc phục lỗi này chúng ta nên dùng khai báo trễ (late binding). Ví dụ:
```
Dim gcnAccess As Variant
Set gcnAccess = CreateObject("ADODB.Connection")
sConnect = .... 'Chuổi kết nối
With gcnAccess
        .Mode = 3    'i.e adModeReadWrite
        'Neu sau thoi gian nay ma khong ket noi duoc se bao loi
        'If after this time pass, the error come out
        .ConnectionTimeout = 30
        'CursorTypeEnum
        'adOpenDynamic     = 2
        'adOpenForwardOnly = 0
        'adOpenKeySet      = 1
        'adOpenStatic      = 3

'The CursorLocationEnum:
        'adUseClient = 3
        'adUseServer = 2
        .CursorLocation = 3    'adUseClient
        .ConnectionString = sConnect
        .Open
End With
'Sau khi kết nối được rồi thì sẽ thực hiện truy xuất
'
```
Đối với ví dụ này ta có thể chuyển thao tác kiểm tra việc kết nối thành công hay không vào một hàm như sau:
```
Public eConnection As Variant

Function lConnection(sFullFilePath As String) As Long
    Dim sConnection As String

'Gia su rang khong co ket noi duoc
    lConnection = 0
    'Kiem tra sFileName co ton tai hay khong?
    If Not FileExists(sFullFilePath) Then
        lConnection = -1
        GoTo ErrorExit
    End If

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                  sFullFilePath & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
    Set eConnection = CreateObject("ADODB.Connection")
    With eConnection
        .Mode = 3    'Tuc la adModeReadWrite
        'Neu sau thoi gian nay ma khong ket noi duoc se bao loi
        .ConnectionTimeout = 30
        'CursorTypeEnum
        'adOpenDynamic     = 2
        'adOpenForwardOnly = 0
        'adOpenKeySet      = 1
        'adOpenStatic      = 3

'The CursorLocationEnum:
        'adUseClient = 3
        'adUseServer = 2
        .CursorLocation = 3    'adUseClient
        .ConnectionString = sConnection
        .Open
    End With
    'Neu thanh cong thi gan la 1
    lConnection = 1
    'Dong ket noi
    eConnection.Close

ErrorExit:

Exit Function

ErrorHandler:
    'Cac thao tac kiem tra loi o day
    'Cac loi khac thi lConnection=-2
    lConnection = -2
    Resume ErrorExit

End Function

' fname là đường dẫn bao gồm cả tên tập tin
Function FileExists(fname) As Boolean
    FileExists = Dir(fname) <> ""
End Function
```
Hàm
```
lConnection
```
sẽ trả về:
- 1: nếu kết nối thành công
- 0: nếu kết nối không thành công
- -1: kết nối không thành công do tập tin không tồn tại
- -2: kết nối không thành công bởi những lỗi khác
Các giá trị này thì tùy bạn, bạn cũng có thể viết chỉ 2 giá trị thôi là 0 (không kết nối được) và 1 (kết nối thành công).
Trong hàm này tôi sử dụng khai báo trễ (late biding), như vậy nó sẽ không phục thuộc vào phiên bản của ADO.

Sau đó là việc chúng ta viết một hàm để lấy về một Recordset dựa trên câu lệnh SQL ta truyền cho hàm này. Như vậy công việc của chúng ta sẽ đơn giản hơn với việc viết các hàm, thủ tục để thực hiện các chức năng chúng ta thường sử dụng trong quá trình kết nối.

Lê Văn Duyệt

To Levanduyet
Cám ơn bạn cung cấp khá nhiều kiến thức hay và rất cơ bản.
Riêng về Set cnEx = New ADODB.Connection gây lỗi thì mình nghĩ khai báo bằng mã lệnh là khai báo trễ (Run-time) chứ, Khai sớm là đặt cố định trên Form khi thiết kế. Mặt khác, mình làm như vậy kể cả trong VB6 dịch ra file *.exe sử dụng khá nhiều máy thấy bình thường.

Nay có điều không hiểu là cũng nhưng câu lệnh đó với điều kiện cơ bản như nhau thì có máy hiểu còn 1 số không hiểu. Phiên bản ADO thì hầu như máy nào cũng có 1 loạt các phiên bản trong máy.

Như tôi đã nói, khi bạn tham chiếu đến đối tượng ADO theo kiểu khai báo sớm (early binding) thì nếu phiên bản của máy người dùng khác với phiên bản của máy người viết, thì thông báo lỗi cũng xãy ra. Còn về việc khai báo sớm, khai báo trễ, lợi ích hay khó khăn của chúng thì chúng ta đã bàn nhiều trên GPE.

Bạn cũng có thể tham khảo các bài viết, so sánh sau:
https://word.mvps.org/fAQs/InterDev/EarlyvsLateBinding.htm
https://www.dicks-clicks.com/excel/olBinding.htm
https://blogs.msdn.com/davidklinems/archive/2006/11/27/what-is-late-binding.aspx

Lê Văn Duyệt

Reply
hands says:

01/02/2023 at 7:55 pm

1. Anh sealand xem thử code bên dưới nhé:
Và anh hãy bỏ đoạn "On error Resume Next" đi, để còn biết nó lỗi ở đâu mà sửa!
==> nghĩa là query trong Excel không dùng "*" mà phải dùng "%" mới được!%#^#$

2. Thêm vào đó, muốn truy xuất dữ liệu từ 1 file excel theo code bên dưới thì file excel nguồn phải đặt NAME cho vùng dữ liệu nguồn đó! ==> Với cách này, 1 sheet nguồn ta có thể có nhiều bảng tương ứng với nhiều NAME đặt cho nhiều vùng khác nhau trên sheet nguồn đó; và có thể truy xuất từ nhiều bảng này ra file khác theo code bên dưới.
(như trong file đính kèm, ta vào Insert/Name/Define sẽ thấy 1 Name có tên "DATA" với địa chỉ là vùng dữ liệu của sheet("DATA"); Ta xóa thử Name này đi thì code sẽ báo lỗi vì không tìm thấy bảng DATA nào cả!)

mySQL = "SELECT * FROM [DATA]" & Chr(13) & "WHERE DATA.tk like '111%'"

– Tuy nhiên không phải vì thể mà không giải quyết được, chúng ta có thể thêm "$" vào sau tên sheet của file –> excel sẽ tự coi sheet này là bảng luôn (giống như cách anh sealand phát biểu lệnh SQL ban đầu) ==> Tuy nhiên, với cách này, 1 sheet sẽ chỉ là 1 bảng dữ liệu mà thôi:

mySQL = "SELECT * FROM [DATA$]" & Chr(13) & "WHERE tk like '111%'"

3. Em có thêm phần ghi chú trong code để người mới tiếp cận ADO dễ hình dung các bước tạo connection và truy xuất dữ liệu từ cơ sở dữ liệu nguồn ban đầu!
```
Sub Chepdl()
Dim FName As String, cnEx As Variant, RecEx As Variant, mySQL As String
'Chuan bi xuat du lieu:
a = MsgBox("Chep Du lieu sang noi khac!?", vbInformation + vbYesNo, "Info")
If a = vbYes Then
'Lay File hien hanh lam file du lieu Nguon [Source data]:
FName = ThisWorkbook.Path & "" & ThisWorkbook.Name
'Tao ket noi voi du lieu nguon:
Set cnEx = New ADODB.Connection
cnEx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FName & _
";Persist Security Info=False; Extended Properties=Excel 8.0;"
cnEx.Open
'Truy xuat du lieu [records] bang cau lenh SQL:
Set RecEx = New ADODB.Recordset
mySQL = "SELECT * FROM [DATA]" & Chr(13) & "WHERE DATA.tk like '111%'"
RecEx.Open mySQL, cnEx, adOpenKeyset, adLockOptimistic
'Chep du lieu truy xuat duoc qua sheet2:
Sheet2.Cells.ClearContents
Sheet1.[1:1].Copy Sheet2.[1:1]
Sheet2.[A2].CopyFromRecordset RecEx
'Refresh lai hai bien cnEx va RecEx:
RecEx.Close: Set RecEx = Nothing
cnEx.Close: Set cnEx = Nothing
b = MsgBox("Du Lieu da chep xong!", vbInformation + vbOKOnly, "Info")
Sheet2.Activate
Else
Exit Sub
End If
End Sub
```
Reply
hands says:

01/02/2023 at 9:16 pm

Sao em thay code thành mySQL = "SELECT * FROM [DATA$]" & Chr(13) & "WHERE tk like '111%'"mà khi đánh thêm dữ liệu nó không cập nhật nhỉ?

Đánh thêm dữ liệu xong bạn có lưu file lại hay chưa!? Mà nếu có lưu hay không lưu thì đâu có ảnh hưởng gì đến việc lấy dữ liệu đâu ta!? Bạn nói rõ "nó không cập nhật" thì "nó" là cái gì không cập nhật!? Có ví dụ cụ thể càng tốt!?

Tiếc quá mình đã làm như bài #10 nhưng vẫn không được, nó vẫn báo lỗi như trên. Phần này hay quá, mình rất muốn tìm hiểu về nó. :((

Bạn xem lại thêm ở đâu, sheet nguồn mới OK , chứ sheet đích thì nó y lệnh xóa thẳng tay trước khi nhập mới. Vậy bạn có nhập bao nhiêu cũng chẳng còn.
To Quoc huy: Thật khó hiểu với trường hợp của bạn vì với ADODC chỉ cần có Microsoft ActiveX Data Ojects 2.n là chạy tốt rồi.

to: sealand
Mình đã làm được rồi.
Bằng cách khai báo thêm như sau:
"Dim cnex, recex As Variant
Dim k As Long
Dim mypath As String"
Nếu ai chưa làm được thì thử làm như mình xem.
Thân

=========================

Mình thêm các dòng dữ liệu bên dưới các dòng trong sheet DATA thì sau khi chạy prosese thì các dữ liệu mình đánh thêm không được cập nhật. mà theo như lệnh mySQL = "SELECT * FROM [DATA$]" & Chr(13) & "WHERE tk like '111%'" thì cả bảng phải được chọn chứ?. À bạn có thể giải thích Chr(13) & "WHERE tk like '111% có nghĩa là gì không? thanks nhiều.

Vẫn cập nhật được trừ khi bạn thêm vào các dòng có mã TK không phải bắt đầu bằng "111".

Chr(13) là ký tự xuống dòng đó bạn.
[Where tk like '111%'] có thể phát biểu là: "khi tk bắt đầu bằng '111' "

Reply
hands says:

01/02/2023 at 9:19 pm

Ví dụ 2 vê ADODC.

Bài này, mình thêm 1 ví dụ thô sử dụng ADODC để trích rút dữ liệu từ chính file Excel này để thiết lập báo cáo, chưa thêm phần nội lực của Excel. Điều mình muốn đề cập ở đây không phải giới thiệu kỹ thuật ADODC (Vì mình còn kém về vấn đề này lắm, nhưng không lo với sự hỗ trợ của GPE mình cho là không phải không làm được) mà mình muốn bàn là giải pháp nâng cao khả năng và có thể sử dụng Exc phục vụ công tác Kế toán thống kê mà thôi.
Trước đây, qua sách vở quảng bá và thực tế mình đã viết file Kế toán trên Exc khá kỳ công. Khi ở dạng Demo vài chục dòng thì chương trình trôi chảy, sổ sách báo báo cáo nhanh chóng, đẹp đẽ. Nhưng đau nhất lại là cuối năm, khi thời gian gấp rút không làm lại được với vài ngàn chứng từ (Tương ứng hàng chục ngàn dòng data) thì file sinh sự ỳ ra không chạy nổi, nhiều khi đơ luôn. Mỗi khi nhập 1 ô phải đợi mãi mới cập nhật được. Lý do, từ việc liên kết công thức từ quá nhiều sổ sách báo cáo đến sheet data. Biết bao nhiêu phép tính thường trực mỗi khi file hoạt động, nó làm cạn kiệt tài nguyên tưởng là khổng lồ của máy tính hiện nay.
Nhưng với ADODC trích dữ liệu xong thì cắt quan hệ với dữ liệu nguồn, các báo cáo gần như độc lập thì việc tạo thêm bao nhiêu báo cáo có hạn chế gì mấy, trong khi dùng công thức thì phải đắn đo mỗi báo cáo phải hy sinh bao nhiêu tốc độ. Mình mạn phép ví Excel -ADODC với khả năng kết hợp SQL thì như các cung thủ thời Tam Quốc được trang bị thêm kính La ze.
Mình xúi vậy thôi, nhưng các bạn cứ thử xem.
(Dữ liệu tháng 4/2009 các ô từ ngày, đến ngày thì bạn gõ tuỳ ý trong tháng 4)

Cám ơn Bác, rất hay, vừa thay AdFi vừ sumif từ ADO này.
Nhưng có điều chạy hay treo máy, chắc chiếm nhiều bộ nhớ.
Em nghĩ nên gám 1 Button để thực thi lệnh, dùng Event hay treo.
Hay là Bác thêm giữa phần
Sheet3.Rows("5:100").ClearContents
Sheet3..CopyFromRecordset recex

Thành
With Application
.EnableEvents = False
End With
Sheet3.Rows("5:100").ClearContents
Sheet3..CopyFromRecordset recex
With Application
.EnableEvents = True
End With

Thu Nghi à, không phải ADO chiếm nhiều bộ nhớ đến nỗi treo máy đâu vì trong các phần mềm đôi khi đồng thời tồn tại vài RecordSet cùng lúc để sử lý mà có sao đâu (Hơn nữa, dữ liệu ví dụ đâu có nhiều). Mình nghĩ cái chính là code ví dụ nên độ tùy biến chưa cao khi sang máy khác hay bị trục trặc hoặc không hoạt động (Ở máy của mình thì những cái sổ lớn như 111,511 thì chỉ là 1 cái chớp màn hình). Nếu cẩn thận thì dùng code của anh Duyệt để kết nối thì an toàn hơn. Nhưng khổ nỗi ví dụ gửi lên gửi xuống mà nó lớn quá cũng không ổn.

Cái Group by trong ví dụ nó tương quan với Sumproduct cơ (3 điều kiện cơ đấy: Tài khoản, từ ngày, đến ngày) . Sumif sao làm nổi nếu không chuyển sang công thức mảng (Mà đã nói đến mảng thì mình lại ghê vì ít thì được chứ nhiều thì ngồi đếm % calculate là thường)

Phần thêm của Thu Nghi là đúng quá chứ. Trong ví dụ để ngắn gọn nên mình áp roẹt 1 cái là Sheet3.Rows("5:100").ClearContents chứ thực tế phải xác định dòng cuối cùng cụ thể rồi xóa mới chính xác chứ. Rồi còn bẫy và sử lý lỗi nữa.

Ủa nhưng mình đã cho tạo ra hộp thoại tạo ra FName riêng, mình nạp đường dẫn trước rồi mới nạp list sau mà sao nó không lấy FName của bước nạp đường dẫn nhỉ? sao 2 cái FName ở bước chọn đường dẫn và bước nạp list không liên kết với nhau nhỉ?

To [URL='https://www.giaiphapexcel.com/forum/member.php?u=184553'%5Dchallenge98:

Mình sửa lại 1 số chỗ theo yêu cầu của bạn, bạn xem lại nhé.
Nhưng làm sao lại phải thêm 1 cái form trung gian để làm gì nữa mình tham gia càng bớt càng tốt.

Cảm ơn sealand rất đúng ý mình à nhờ mọi người giải thích cho mình đoạn code này với mình chưa hiểu lắm.

If recex.RecordCount > 0 Then
recex.MoveFirst
k = 0
Do While Not recex.EOF
ListBox1.AddItem recex.Fields(0)
ListBox1.List(k, 1) = recex.Fields(1)
k = k + 1
recex.MoveNext
Loop

Mình thấy trong giáo trình SQL có viết muốn chọn tất cả các cột có dữ liệu chỉ cần dùng truy vấn select * from lop là được mà sao không được nhỉ?
Thanks!

If recex.RecordCount > 0 Then 'Nếu recex có DL thì làm , không thì thôi
recex.MoveFirst 'Chuyển con trỏ về record đầu tiên
k = 0 'Đặt biến k=0
Do While Not recex.EOF 'Bắt đầu vòng lặp nếu recex ở cuối thì thôi
ListBox1.AddItem recex.Fields(0) 'Thêm 1 mục vào List bằng Field thứ nhất
ListBox1.List(k, 1) = recex.Fields(1) 'Thêm cột thứ 2 cho mục thứ k của List bằng Field thứ hai
k = k + 1 'Đặt biến k tăng them 1
recex.MoveNext 'Chuyển con trỏ về record tiếp theo
Loop 'Lặp lại

2/ Câu lệnh SQL thì cũng y chang ý bạn đấy thôi
recex.Open "select * from [Sheet1$] ", cnEx, adOpenKeyset, adLockOptimistic

sealand này mình thấy một số người lại chuyển dữ liệu thành Array rồi từ array đưa vào listbox chứ không add từng cột( Field). 2 cách này thì cách nào hơn ( nhanh và đơn giản hơn).

Cho mình hỏi cái là file csv cũng là một trong những file của excel mà sao khi nạp vào thì không chạy nhỉ?

1/Theo mình nạp trực tiếp gọn hơn đỡ phải qua khâu nạp mảng, đỡ phải dọn dẹp vì với dữ liệu lớn thì cái biến mảng ấy khổng lồ, nếu quên reset biến thì nó chiếm bộ nhớ không nhỏ.
2/Nó không chạy vì phần mở rộng mà thôi, bạn đã khai đuôi trong chuỗi connect rồi. Bạn đổi phần mở rộng thành .xls xem.

Không phai đâu sealand à mình nạp thủ không được đố sealand nạp chạy được file này

à mình xin nói thêm là cái file data.xls trên là file data.txt rename thành khi nạp no không nhận có cách nào khắc phục không cả nhà?

Sub chepdl2()
ng1 =
ng2 =
On Error Resume Next
FName = ThisWorkbook.Path & "" & ThisWorkbook.Name
Set cnEx = New ADODB.Connection
cnEx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
cnEx.Open
Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic
Sheet3.Rows("5:100").ClearContents
Sheet3..CopyFromRecordset recex
recex.Close
Set recex = Nothing
cnEx.Close
Set cnEx = Nothing

End Sub

Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic
Bác cho em hỏi [Data$] có thể là 1 range không.
Ý là mình xác định số dòng cột để mà tổng hợp. Nếu dùng trên Ex 2007, vậy là nó lấy hết bảng tính. Sợ lớn không.
Từ này em sẽ dùng cái này thế AdFi thử.
Cám ơn Bác nhiều.

Nếu muốn truy vấn dữ liệu trên một Range thì làm thế này

SELECT * FROM [sheet1$A4:H100]…

Thông thường nên đặt tên/Name NKC= sheet1!$A$4:$H$100

Khi đó ta có thể viết câu lệnh SQL

SELECT * FROM NKC…

Reply
hands says:

01/02/2023 at 9:21 pm

Sub chepdl2()
ng1 =
ng2 =
On Error Resume Next
FName = ThisWorkbook.Path & "" & ThisWorkbook.Name
Set cnEx = New ADODB.Connection
cnEx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
cnEx.Open
Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic
Sheet3.Rows("5:100").ClearContents
Sheet3..CopyFromRecordset recex
recex.Close
Set recex = Nothing
cnEx.Close
Set cnEx = Nothing

End Sub

Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from [Data$]where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic
Bác cho em hỏi [Data$] có thể là 1 range không.
Ý là mình xác định số dòng cột để mà tổng hợp. Nếu dùng trên Ex 2007, vậy là nó lấy hết bảng tính. Sợ lớn không.
Từ này em sẽ dùng cái này thế AdFi thử.
Cám ơn Bác nhiều.

1/ Truy vấn toàn bộ Worksheet
"SELECT * FROM [MySheet$]"

2/ Truy vấn một dãy ô :
"SELECT * FROM [MySheet$A1:G100]"

3/ Truy vấn một dãy được đặt tên :
"SELECT * FROM MyNamedRange"

Sao vận dụng đặt name vào bài SO KE TOAN của anh Sealand trong topic này mà nó không chạy. Các bác xem hộ sai chỗ nào.
Sub chepdl2()
ng1 =
ng2 =
Dim endR As Long, myRng As Range
On Error Resume Next
FName = ThisWorkbook.Path & "" & ThisWorkbook.Name
Set cnEx = New ADODB.Connection
With Sheets("Data")
endR = .Cells(65000, 1).End(xlUp).Row
Set myRng = .Range("A1:H" & endR)

End With
cnEx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
cnEx.Open
Set recex = New ADODB.Recordset
recex.Open "select tk, sum(psno), sum(psco)from myrng where Ngay >=#" _
& ng1 & "# and Ngay<=#" & ng2 & "# group by tk ", cnEx, adOpenKeyset, adLockOptimistic

Sheet3.Rows("5:100").ClearContents
Sheet3..CopyFromRecordset recex
recex.Close
Set recex = Nothing
cnEx.Close
Set cnEx = Nothing

End Sub

Tại sao file này chạy trên máy tôi lại không được nhỉ? Đã thay 2.0 bằng 2.8 nhưng khi thay đổi ngày thì code không chạy. Máy cài WinXP SP3, Office 2003 và 2007. Hay tại cài hai cái Off trên một máy nhỉ? Ai biết chỉ giùm
Thân

Mới nhờ PTM0412 hướng dẫn và rút ra 1 nhận xét.
Phải đặt name chớ không phải
Dim MyRng as range
Set MyRng=…

Mà phải là
With Sheets("Data")
endR = .Cells(65000, 1).End(xlUp).Row
.Range("A1:H" & endR).Name = "myRng"
End With

Lúc này ADO mới xem dòng 1 là tên field.
Và phải thêm "" thành thì nó mới chạy.

Trong các loại biến thì biến Variant là loại biến đáng dè chừng nhất, mình cũng chưa thấy tài liệu nào chỉ dẫn nó biến tướng đến đâu. Trong code của Thu Nghi kêu nó không chạy, nhưng đổi dạng biến 1 chút thì nó lại chạy (Không phải thay đổi bất cứ điều gì nữa):

Dim MyRng As Range <—đổi thành—> Dim MyRng As Variant

Đây là một ví dụ ứng dụng ADODB với câu lệnh SQL lồng (dùng Union All). Các anh chị và các bạn tham khảo code bên dưới và xem file đính kèm. Đã test thử với cấu trúc DATA như file đính kèm, khoản 10.000 record lọc chưa tới 15 giây.
```
Sub GetDetail()
'---------------------------------------------------------------------------
'FName: Database Name including Path (ex: "D:MyDatabaseMyFile.xls")
'cnnEx: Connection string into external database
'recEx: Recordset
'mySQL: SQL statement
'---------------------------------------------------------------------------
Dim FName As String, mySQL_Dr As String, mySQL_Cr As String, mySQLDetail As String
Dim mPeriod As Long, mAcctID As String
Dim cnnEx As New ADODB.Connection
Dim RecEx As New ADODB.Recordset
'---------------------------------------------------------------------------
FName = ThisWorkbook.FullName
mPeriod = Sheets("Detail").[B6].Value   'Ky bao cao can loc
mAcctID = "'" & Sheets("Detail").[B5].Text & "%'"   'Tai khoan can loc, co the loc theo do dai ky tu tai khoan
'Tao Ket noi voi file du lieu nguon:
cnnEx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FName & _
                            ";Persist Security Info=False; Extended Properties=Excel 8.0;"
'---------------------------------------------------------------------------
mySQL_Dr = "Select a.TKNo as TaiKhoan, a.SoCtu, a.NgayCtu, a.DienGiai, a.TKCo as TKDoiUng, a.Thanhtien as GhiNo, 0 as GhiCo, 0 as REF FROM [DATA$] AS a" & Chr(13)
mySQL_Dr = mySQL_Dr & "WHERE a.Period= " & mPeriod & " AND a.TKNo LIKE " & mAcctID
mySQL_Cr = "Select a.TKCo as TaiKhoan, a.SoCtu, a.NgayCtu, a.DienGiai, a.TKNo as TKDoiUng, 0 as GhiNo, a.ThanhTien as GhiCo, 1 as REF FROM [DATA$] AS a" & Chr(13)
mySQL_Cr = mySQL_Cr & "WHERE a.Period= " & mPeriod & " AND a.TKCo LIKE " & mAcctID
'---------------------------------------------------------------------------
mySQLDetail = mySQL_Dr & Chr(13) & "Union all" & Chr(13) & mySQL_Cr
a = MsgBox("You will get the data by runing this SQL statement: " & Chr(13) & "----------" & Chr(13) & mySQLDetail & Chr(13) & "----------", vbInformation + vbYesNo, "Info")
If a = vbYes Then
RecEx.Open mySQLDetail, cnnEx, adOpenKeyset, adLockOptimistic
'---------------------------------------------------------------------------
    'Neu query khong tim thay du lieu thi thoat ra, khong xoa du lieu cu:
    If RecEx.EOF Then
        a = MsgBox("Hic!No record found!", vbCritical + vbOKOnly, "Info")
        Exit Sub
    Else
        Sheets("Detail").[9:65536].Delete
        Sheets("DETAIL").[A9].CopyFromRecordset RecEx
        'Refresh lai hai bien cnEx va RecEx:
        RecEx.Close: Set RecEx = Nothing
        cnnEx.Close: Set cnEx = Nothing
        b = MsgBox("Data has already been exported to sheet(Detail)!", vbInformation + vbOKOnly, "Info")
        Sheets("DETAIL").Activate
    End If
Else
c = MsgBox("Cancel query by user!Hehe...!See you later! ;) --> ca_dafi", vbCritical + vbOKOnly, "Info")
Exit Sub
End If
End Sub
```
File PDF đính kèm hướng dẫn sơ lược cách sử dụng ADODB một cách đơn giản nhất! Mời mọi người tham khảo!

Reply
hands says:

01/02/2023 at 9:23 pm

Mình thử nghiên cứu ADO về lọc duy nhất và thống kê theo mã lọc.
Qua sự hướng dẫn của Ptm012 mình làm thử file sau
Tính sotien theo ngày của từng Makh và gán tên KH (Vlookup). Nghĩa là vừa lọc duy nhất theo ngày, theo MaKH, cộng số tiền và gán thông tin KH và report.
Sub TongHop()
Dim FName As String
Dim cnnEx As New ADODB.Connection
Dim RecEx As New ADODB.Recordset
Dim endR As Long, mySQL As String
With Sheet2
endR = .Cells(65000, 1).End(xlUp).Row
.Range(.Cells(1, 1), .Cells(endR, 3)).Name = "MyData"
End With
With Sheet1
endR = .Cells(65000, 1).End(xlUp).Row
.Range(.Cells(1, 1), .Cells(endR, 3)).Name = "dmkh"
End With

FName = ThisWorkbook.FullName
cnnEx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FName & _
";Persist Security Info=False; Extended Properties=Excel 8.0;"
mySQL = "select Mydata.MaKH, dmkh.TenKH, mydata.NgayGD, sum(Mydata.sotien) from "
mySQL = mySQL & "inner join on dmkh.MaKH = Mydata.MaKH "
mySQL = mySQL & "group by Mydata.MaKH, dmkh.TenKH, mydata.ngaygd "
RecEx.Open mySQL, cnnEx, adOpenKeyset, adLockOptimistic
With Sheet3
..ClearContents
..CopyFromRecordset RecEx
End With
RecEx.Close: Set RecEx = Nothing
cnnEx.Close: Set cnEx = Nothing
Sheet3.Activate
End Sub

Các bạn triển khai tiếp thêm gán theo định mức.
Cám ơn.

Bài này, mình đề xuất 1 phương án trích tạo sổ sách báo cáo sao cho thật linh hoạt và code ngăn nhất. Đông thời cũng là code tạo danh sách duy nhất khá gọn ( 3 trong 1:Các ngày có PS, Các TK có PS, Các Đối tương có PS). Đây chỉ là phương án thôi nên còn hạn chế và lỗi. Chỉ yêu cầu người dùng biết chút về viết SQL là chủ động tạo sổ sách, báo cáo rồi. Xin lỗi mình chưa trang trí tạo tiêu đề cho sổ.
Riêng phần kết nối mình đưa vào biến công cộng, khi cần dùng cứ việc gọi. Ta dùng loạt bài đầu để rút dữ liệu từ các file khác nhau là có thể tạo file sosach dùng chung. Như vậy, ta loại được sổ sách báo cáo ra khỏi file nhập dữ liệu làm cho file dữ liệu hoàn toàn tự do và nhẹ nhàng phục vụ mục đính chính là nhập dữ liệu.
Các bạn xem và góp ý cho mình với.

Nếu trường hợp Data mà số dòng > 65.536 thì chuyển sang Excel 2007 sẽ bị lỗi kết nối.
cnex.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Persist Security Info=False; Extended Properties=Excel 8.0;"
cnex.Open
Và câu kết nối trên em nghĩ nếu thay bằng câu sau cho dễ hiểu

Dim ConnectionString
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FName & ""
ConnectionString = ConnectionString & ";Persist Security Info=False; Extended Properties=Excel 8.0;"""
cnex.Open ConnectionString

Bác khắc phục giúp. Cám ơn Bác!

Mình không có Excel2007 nên không test được, Thu Nghi tham khảo [URL='https://www.connectionstrings.com/excel-2007'%5Dở đây xem

Sau khi tham khảo link và có sự HD của Bác Mỹ (PTM0412) em đã làm OK rồi.
Các Bác giỏi thật.
Chỉ cần thay
Provider=Microsoft.Jet.OLEDB.4.0
và
Extended Properties=Excel 8.0

Thành
Provider=Microsoft.ACE.OLEDB.12.0
Và
Extended Properties=Excel 12.0

Em đang cân nhắc các tham số HDR=Yes/No hay IMEX=1.
Các Bác demo cho các em học hỏi.
Xin cám ơn chân thành!

HDR = 1 để xác định dòng đầu là tên field

HDR = 0 để lấy luôn dòng đâu như là 1 record, khi copy kết quả xuống sheet.

Nếu dùng HDR = 0 thì với dữ liệu kiểu số, mà record đầu là tên filed, là text, nguyên field sẽ bị coi là text.

Do đó phải dùng IMEX = 1, mục đích để Excel nhận dạng lại type của dữ liệu trên cả field, không chỉ căn cứ trên dòng đầu.

Nói thêm, khuyến cáo nên dùng IMEX = 1 dù cho HDR = Yes hay No, để ADO không nhận dạng type dữ liệu bị sai, nhất là khi bảng tính định dạng General, và/ hoặc field dữ liệu bỏ trống record đầu.

Lý do là ADO khi đọc dòng dầu thấy ô trống, nó sẽ đoán (guess) 1 trong các kiểu là:
– Numeric và giá trị zero
– Text và giá trị blank
– DateTime và giá trị blank

Mà không phải lúc nào cũng đoán đúng.

Tôi có 2 Sheet:
Sheet1: Mua

, , , , , [Địa chỉ], , , , , , . . .

Sheet2: Trả tiền
, , , , ,

Tôi muốn Tổng hợp số liệu từ 2 bảng trên vào Sheet_TONGHOP
có các tiêu đề sau:

, , , , , , , CONLAI]

Vậy tôi phải viết code ra sao Chỉ giùm với các bác ơi !!!!!!

NGAY_VS là ngày gì?
SO_CT là số CT của mua hay trả tiền?
NGAY_CT là ngày CT mua hay ngày trả tiền?

Reply