ADO-Thao tác DB với Excel (chỉ định phạm vi, ghi dữ liệu)

  • Thread starter bvtvba
  • Ngày gửi
B

bvtvba

Guest
Lời nói đầu
Hiện nay ADO đã được sử dụng rất nhiều trong các topic hỗ trợ code. Diễn đàn THVBA là một trong những diễn đàn đi đầu trong việc lý thuyết hóa, tiêu chuẩn hóa các đoạn code mẫu, phục vụ cho việc code được nhanh chóng. Song song với các chủ đề đã có trên diễn đàn, mình hi vọng topic này cũng sẽ được đón nhận nồng nhiệt.
Trong bài viết này, chúng ta sẽ sử dụng ADO (ActiveX Data Objects) và thao tác với file Excel thông qua cấu trúc lệnh SQL. Mình sẽ tổng hợp dưới dạng phương pháp để các bạn dễ theo dõi.
Đây là phương pháp đọc dữ liệu sheet và làm mới, cập nhật dữ liệu Excel với tư cách là một Database, tốc độ xử lý nhanh nên được mọi người ưa chuộng.

ADO ( ActiveX Data Objects ) là gói lệnh (API) dùng để thao tác database. Điều này chắc là dân IT sẽ nắm rõ hơn mình. Mình chỉ là người ngoại đạo, chuyên ngành không phải là IT.

1. Kiến thức cơ bản
1.1 Coi toàn bộ sheet Excel là một table và lấy dữ liệu
1.2 Coi một phần của sheet Excel là một table và lấy dữ liệu
2. Giải thích code
2.1 Tạo một biến đối tượng mang chức năng của thư viện bên ngoài
2.2 Kết nối ADO
2.3 Mở dữ liệu được trích xuất bằng câu lệnh SQL trong tập bản ghi
2.4 Kiểm tra nội dung của tập bản ghi
3. Cập nhật dữ liệu cho WorkSheet
3.1 Mở tệp tin (file) và ghi bản ghi (phương thức CopyFromRecordset)
3.2 Ghi bản ghi mà không cần mở tệp (phương pháp Update, UpdateBatch)

Dẫn nguồn tham khảo : Cập nhật sau.
 

tuhocvba

Administrator
Thành viên BQT
1. Kiến thức cơ bản
1.1 Coi toàn bộ sheet Excel là một table và lấy dữ liệu

Đầu tiên bạn hãy tạo một file Excel có nội dung như sau:
Bạn cần đăng nhập để thấy đính kèm

Trong bài viết này tôi sẽ dụng ý tạo ra một file Excel chứa dữ liệu và một file Excel VBA. Chúng được lưu trong cùng một folder.
Dụng ýFile nameĐường dẫn
VBA Excel: Tôi sẽ viết code trên file nàyADOTest.xlsmD:\VBA\ADOTest.xlsm
File data chứa dữ liệuTHVBA_TestTable.xlsxD:\VBA\THVBA_TestTable.xlsx
1-1-1 Không thiết định Reference
Mã:
Sub Sample1()
    'Tao ket noi voi ben ngoai
    Dim cn As Object
    Dim rs As Object
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    'Ket noi ADO
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0"
    cn.Open ThisWorkbook.Path & "\" & "THVBA_TestTable.xlsx"

    'Thuc thi SQL
    rs.Open "SELECT * FROM [Sheet1$] ORDER BY Gia", cn

    'Xac nhan noi dung lay duoc(Recordset)
    Do Until rs.EOF
        Debug.Print rs!SanPham & ", " & rs!Gia
        rs.MoveNext
    Loop

    'Giai phong bo nho
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub
Chương trình nói trên sẽ dò tìm trên bảng biểu Excel, nó đọc từng dòng dữ liệu và lấy dữ liệu theo tên cột dữ liệu.
Bản ghi (tương đương một dòng dữ liệu của Excel) sẽ được cất trong đối tượng rs.
Nội dung bản ghi có thể được lấy ra theo cấu trúc rs![ColumnName]
Kết quả:
Bạn cần đăng nhập để thấy đính kèm

Xin đừng lo lắng vì những gì đang hiển thị có phần rối rắm, là do tiếng việt có dấu không được hiển thị tốt trên Immediate mà thôi.
Trong cấu trúc SQL khi sử dụng cấu trúc ORDER BY có nghĩa rằng dữ liệu sẽ được sắp xếp theo chiều hướng tăng dần.
Như kết quả trên các bạn cũng thấy các sản phẩm có giá được sắp từ thấp tới cao.
 

tuhocvba

Administrator
Thành viên BQT
1-1-2 Trường hợp thiết định reference
Bạn cần đăng nhập để thấy đính kèm

Tiếp theo bạn tìm tới thư viện có dạng như sau Microsoft ActiveX Data Object X.X Library và tích chọn vào nó và ấn OK.
Bạn cần đăng nhập để thấy đính kèm

Khác với ví dụ trước, lần này, trước khi chương trình được thực thi thì thư viện ActiveX Data Object đã được đọc (nạp).
Lúc này Code sẽ như sau :
Mã:
Sub Sample2()
    'Tao ket noi voi ben ngoai
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    

    'Ket noi ADO
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0"
    cn.Open ThisWorkbook.Path & "\" & "THVBA_TestTable.xlsx"

    'Thuc thi SQL
    rs.Open "SELECT * FROM [Sheet1$] ORDER BY Gia", cn

    'Xac nhan noi dung lay duoc(Recordset)
    Do Until rs.EOF
        Debug.Print rs!SanPham & ", " & rs!Gia
        rs.MoveNext
    Loop

    'Giai phong bo nho
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub
Kết quả hoàn toàn giống với ví dụ trước.
 
1.2 Coi một phần của sheet Excel là một table và lấy dữ liệu
Bây giờ tôi sẽ sửa lại file data như sau.
Bạn cần đăng nhập để thấy đính kèm

Vùng khoanh đỏ là nơi tôi muốn thao tác lấy dữ liệu. Chính xác là vùng B4 : F11 .
Code lúc này như sau :
Mã:
Sub Sample3()
    'Tao ket noi ADO
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim s  As String
    
    s = ThisWorkbook.Sheets(1).Cells(1, 1) 'Phu Tho

    'Ket noi ADO
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0"
    cn.Open ThisWorkbook.Path & "\" & "THVBA_TestTable.xlsx"

    'Chi dinh pham vi cua mot sheet
    rs.Open "SELECT * FROM [Sheet1$B4:F] WHERE KhuVuc = '" & s & "' ORDER BY Gia DESC", cn

    'Xac nhan noi dung lay duoc (Recordset)
    Do Until rs.EOF
        Debug.Print rs!SanPham & ", " & rs!Gia
        rs.MoveNext
    Loop

    'Giai phong bo nho
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub
Do tôi muốn lấy KhuVuc = Phú Thọ. Đây là từ tiếng việt có dấu, tôi không thể vô tư ghi từ tiếng việt có dấu vào code, nó sẽ tạo ra lỗi, vì vậy tôi ghi ký tự này (Phú Thọ) vào ô A1 sheet 1 của file VBA Excel.
Kết quả:
Bạn cần đăng nhập để thấy đính kèm

Phần code cần lưu ý đó là:
Mã:
rs.Open "SELECT * FROM [Sheet1$B4:F] WHERE KhuVuc = '" & s & "' ORDER BY Gia DESC", cn
Ta coi Sheet1 là Table do đó viết Sheet1$, tuy nhiên sau dấu $ ta viết B4:F chính là chỉ định đây là phần Header của Table. Nó sẽ tự phán đoán được Table là vùng dữ liệu nào sau đó.
Tất nhiên bạn có thể chỉ định là [Sheet1$B4:F11] , nhưng điều này sẽ xảy ra lỗi nếu như sau này bạn thêm dữ liệu vào file Data, nhưng code thì bị bó cứng với phạm vi này.
Ngoài ra, nếu như bên trái và bên trên đều trống, thì ngay cả khi bạn chỉ định là Sheet1$ thì chương trình vẫn có thể tự động phán đoán được vùng dữ liệu cần lấy.
 
2. Giải thích code
2.1 Tạo một biến đối tượng mang chức năng của thư viện bên ngoài
Tự bản thân Excel không có chức năng này, mà chúng ta phải mượn chức năng này từ thư viện bên ngoài. Cụ thể ở đây chúng ta đã sử dụng ActiveX .
Không thiết định thông qua References:
Dim cn As Object
Dim rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Ở đây chúng ta đã sử dụng hàm CreateObject để gọi thư viện bên ngoài, tạo ra hai biến đối tượng là cn và rs.
Trường hợp đã thiết định trong References:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Trong trường hợp này thư viện ActiveX đã được nạp, do đó ta có thể khai báo kiểu biến là
ADODB.Connection và ADODB.Recordset .
Trường hợp này khi viết code sẽ hiển thị ra các gợi ý nếu có, ngoài ra chương trình cũng thực thi nhanh hơn.
Khi chúng ta sử dụng từ khóa New thì biến đối tượng cũng được hình thành.
2.2 Kết nối ADO
Mã:
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.Properties("Extended Properties") = "Excel 12.0"
cn.Open ThisWorkbook.Path & "\" & "THVBA_TestTable.xlsx"
Ba dòng code trên sẽ thành lập kết nối tới file Excel. Hơn thế nữa, chúng ta có thể thao tác với nó bằng ADO với tư cách như là đang thao tác với một database.
Cụ thể hơn ta sẽ đi vào từng dòng code.
2-2-1 Thiết định Provider
Mã:
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
Phần code cn.Provider = là để gọi driver (Provider) sử dụng trong kết nối ADO ra làm việc.
Về cơ bản thì ứng với mỗi phiên bản office ta nên chọn driver cho phù hợp.
Chỉ địnhNội dung
Microsoft.Jet.OLEDB.4.0Office 2003 trở về trước
Microsoft.ACE.OLEDB.12.0Office 2007 tới hiện tại
2-2-2 Thiết định thuộc tính mở rộng Extended Properties
Mã:
cn.Properties("Extended Properties") = "Excel 12.0"
Phần code cn.Properties("Extended Properties") = là để tiến hành thiết định thuộc tính mở rộng Extended Properties.
Về cơ bản khi chúng ta kết nối với những thứ không phải là Database thực sự, ví dụ kết nối tới Excel, CSV,... thì chúng ta phải sử dụng nó.
  • Chỉ định ISAM Version : Phương thức truy cập tuần tự được lập chỉ mục .
Theo ví dụ dưới đây dường như không có vấn đề gì.
Chỉ địnhNội dung
Excel 8.0Office2003 trở về trước
Excel 12.0Office2007 tới hiện tại
TextTrường hợp file csv
  • Chỉ định xem có lấy dòng đầu tiên làm tên cột (tiêu đề^Header) hay không
    Nếu không có chỉ định gì mặc định HDR=Yes , khi đó nó coi dòng đầu tiên là dòng tiêu đề.
Chỉ địnhNội dung
HDR=YesCoi dòng đầu tiên là tiêu đề
HDR=NoCoi dòng đầu tiên là dữ liệu
  • Chi định IMEX(IMPORT EXPORT MODE) : Trong ví dụ code không có sử dụng tới cái này. Trong nhiều đoạn code thực tế cũng ít thấy được sử dụng. Vậy nó là gì?
Chỉ địnhNội dung
IMEX=0Export Mode (Chế độ ghi)
IMEX=1Import Mode ( chế độ đọc)
IMEX=2Link Mode ( Chế độ đọc và ghi)
Giả sử chúng ta chỉ đọc lấy dữ liệu, khi đó chỉ cần chỉ định IMEX = 1. Đây là trường hợp phổ biến trong thực tế.
Trường hợp chỉ định là IMEX = 0, nó sẽ không mở file, mà giống như Access, nó có thể ghi dữ liệu như là thao tác với database.
Sử dụng nhiều chỉ định:
Nếu như có nhiều mục mà bạn muốn chỉ định cùng lúc, hãy phân tách chúng bởi dấu ;
Thiết định nhiều hạng mục cho thuộc tính ExtendedProperties:
cn.Properties("Extended Properties").Value = "Excel 12.0;HDR=Yes;IMEX=1"
2-2-3 Chỉ định file kết nối
Mã:
cn.Open ThisWorkbook.Path & "\" & "THVBA_TestTable.xlsx"
Đây là trường hợp chúng ta kết nối với một file khác được cất chung cùng folder với file VBA Excel.
Trường hợp mà bạn muốn thao tác với chính file VBA Excel nhưng là với một sheet dữ liệu nào đó trên file này, lúc này đường dẫn sẽ là:
Làm việc với Worksheet trên file VBA Excel:
cn.Open ActiveWorkbook.FullName
Ngoài ra chỉ định file kết nối và thực thi kết nối có thể tách biệt như sau:
Mã:
cn.Properties("Data Source") = ThisWorkbook.Path & "\" & "THVBa_TestTable.xlsx"
cn.Open
2-2-4 Kết nối ADO chỉ với một dòng code
Trên đây ta đã có phần hơi dài dòng. Sau đây tôi sẽ kết nối ADO chỉ với một dòng code.
Mã:
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No"""
 
2.3 Mở dữ liệu đã trích xuất bằng SQL bởi Recordset
Recordset được cấu thành bởi hàng và cột, nó giống như Table.
Khi dùng phương thức Open để mở Recordset Object, ta có thể thao tác với dữ liệu đã trích xuất hay với Table.
Đoạn code dưới đây chỉ là kết nối SQL tuy nhiên ngoài ra chúng ta có thể thiết định thêm tham số cho nó :
Mã:
rs.Open "SELECT * FROM [Sheet1$] ORDER BY Gia", cn
Cú pháp chính thức bao gồm tất cả các tham số của phương thức Open này như sau.
Mã:
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Các tham số của phương thức Open được liệt kê dưới đây:
Tham sốNghĩaDiễn giải
SourceDB SourceNguồn dữ liệu
ActiveConnectionKết nốiChỉ định kết nối tới DB
CursorTypeChủng loại cursorChỉ định xem có phản ánh chuyển động của con trỏ hay kết quả cập nhật của những người dùng khác hay không
(adOpenForwardOnly、adOpenKeyset、adOpenDynamic、adOpenStatic )
LockTypeChủng loại LockChỉ định loại khóa được áp dụng cho bản ghi trong quá trình đang biên tập chỉnh sửa
(adLockBatchOptimistic、adLockOptimistic、adLockPessimistic、adLockReadOnly、adLockUnspecified )

OptionsOptions
Chi tiết các tham số này ta sẽ nhắc lại ở phần 3.2
 
2.4 Kiểm tra nội dung của tập bản ghi
Ta có đoạn code mẫu để đọc từng dòng dữ liệu như sau:
Mã:
Do Until rs.EOF
    Debug.Print rs!SanPham & ", " & rs!Gia
    rs.MoveNext
Loop
Đoạn code trên có nghĩa là , khi đi tới bản ghi cuối cùng, rs.EOF sẽ là True, khi đó vòng lặp sẽ kết thúc.
EOF có là từ viết tắt của End Of File. (Ngoài ra ta còn có BOF là Beginning Of File).
Và như vậy, rs![Tên_Trường_Dữ_Liệu] sẽ lấy thông tin với bản ghi tại vị trí con trỏ hiện hành.
Mã:
Debug.Print rs!SanPham & ", " & rs!Gia
Sau khi đọc thông tin bản ghi (Record), nó sẽ không tự động di chuyển tới bản ghi tiếp theo (dòng dữ liệu tiếp theo), do đó ta sử dụng lệnh rs.MoveNext (sử dụng phương thức MoveNext của bản ghi) để di chuyển con trỏ.
TênNội dung
MoveFirst (Phương thức)Di chuyển tới bản ghi đầu tiên
MoveLast (Phương thức)Di chuyển tới bản ghi cuối cùng
MoveNext (Phương thức)Di chuyển tới bản ghi tiếp theo
MovePrevious (Phương thức)Di chuyển tới bản ghi trước đó
Update (Phương thức)Lưu những thay đổi của bản ghi
UpdateBatch (Phương thức)Lưu hàng loạt bản cập nhật đang chờ xử lý
AddNew (Phương thức)Tạo bản ghi mới
Delete (Phương thức)Xóa bản ghi
Find (Phương thức)Tìm dòng dữ liệu thỏa mãn yêu cầu chỉ định
BOF (Thuộc tính)Vị trí con trỏ ở trước bản ghi đầu tiên
EOF (Thuộc tính)Vị trí con trỏ ở sau bản ghi cuối cùng
 
3. Cập nhật dữ liệu cho WorkSheet
Chúng ta có thể mở file Excel và cập nhật dữ liệu cho nó. Tuy nhiên thao tác này thường mất thời gian, do đó ở phần này tôi sẽ giới thiệu các bạn phương pháp không cần mở file Excel, chúng ta sẽ dùng Recordset để kết nối tới file excel và cập nhật dữ liệu cho file Excel.
3.1 Mở tệp tin (file) và ghi bản ghi (phương thức CopyFromRecordset)
Mã:
Sub Sample4()
    'Khai bao som
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Dim wb As Workbook
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "TestTable.xlsx")

    'Ket noi ADO
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0"
    cn.Open ThisWorkbook.Path & "\" & "TestTable.xlsx"


    'Cu phap SQL
    rs.Open "SELECT * FROM [Sheet1$] ORDER BY Gia", cn, adOpenKeyset

    'Ghi du lieu Recordset vao file excel
    wb.Sheets("Sheet1").Cells(2, 1).CopyFromRecordset rs

    'save file
    wb.Close savechanges:=True

    'giai phong bo nho
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub
Khi thực thi code trên, nó sẽ sắp xếp giá theo thứ tự tăng dần.
Trong code trên tôi đã sử dụng phương thức CopyFromRecordset.
Mã:
wb.Sheets("Sheet1").Cells(2, 1).CopyFromRecordset rs
Câu lệnh này tương đương với việc các bạn click chuột phải vào cells(2,1) và chọn paste.
Khi đó dữ liệu recordset sẽ được dán vào đây.
 

vbano1

SMod
Thành viên BQT
3.2 Ghi bản ghi mà không cần mở tệp (phương pháp Update, UpdateBatch)
3.2.1 Cập nhật bản ghi
Đầu tiên chúng ta sẽ sử dụng phương thức Update của Recordset để tiến hành cập nhật từng bản ghi (từng dòng dữ liệu).
3.2.1.1 Cập nhật từng bản ghi
Mã:
Sub Sample5()
    'Tao ket noi, khai bao som
    Dim cn As New ADODB.Connection 'Microsoft ActiveX Data Objects xx Library
    Dim rs As New ADODB.Recordset

    'Ket noi ADO
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0;HDR=Yes;IMEX=0"
    cn.Open ThisWorkbook.Path & "\" & "TestTable.xlsx"

    'Cu phap SQL
    rs.Open "SELECT * FROM [Sheet1$] WHERE Loai = 'Rau'", cn, adOpenStatic, adLockPessimistic

    'Update Recordset
    Do Until rs.EOF
        rs!Gia = Val(rs!Gia) + 1
        rs.Update
        rs.MoveNext
    Loop

    'Giai phong bo nho
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub
Trước khi chạy code:
Bạn cần đăng nhập để thấy đính kèm

Sau khi chạy code:
Bạn cần đăng nhập để thấy đính kèm

Ở nội dung code trên, ta cập nhật vào từng dòng dữ liệu mà chủng loại là Rau, trong đó giá tăng lên 1 đơn vị. Mặc dù chỉ cập nhật giá nhưng mỗi lần chúng ta cập nhật một dòng bản ghi, thì cả Sheet Excel cũng đồng thời được cập nhật.
Cái điều này cũng có thể thực thi giống với DB thông thường.
Trong code trên có điểm quan trọng đó là phần thuộc tính kết nối ADO:
Mã:
cn.Properties("Extended Properties") = "Excel 12.0;HDR=Yes;IMEX=0"
Trong đó phải kể tới thông số IMEX = 0, nó có nghĩa là chế độ ghi vào file Excel. (Export Mode), nhờ đó mà ta có thể làm mới (cập nhật) từ Recordset.
Một điểm quan trọng nữa là phương thức Open của Recordset :
Mã:
rs.Open "SELECT * FROM [Sheet1$] WHERE Loai = 'Rau'", cn, adOpenStatic, adLockPessimistic
Ở đây ta có chỉ định kiểu con trỏ (CursorType) và kiểu khóa (LockType).
Sau đây tôi sẽ thuyết minh từng loại.
TênÝ nghĩaNội dung
adOpenDynamicCon trỏ độngNgười dùng có thể thêm dữ liệu, cập nhật, xóa sẽ được phản ánh (người dùng khác biết được)
adOpenKeysetCon trỏ keysetNgười dùng cập nhật dữ liệu sẽ được phản ánh nhưng thêm hay xóa dữ liệu thì người sử dụng khác không thể truy cập được. (Thường hiệu quả khi số lượng bản ghi lớn)
adOpenStaticCon trỏ tĩnhNgười dùng thêm, xóa, cập nhật sẽ không được phản ánh (người dùng khác không biết được)
adOpenForwardOnlyCon trỏ chuyển tiếp về phía trướcNgười dùng thêm, xóa, cập nhật sẽ không được phản ánh (người dùng khác không biết được). Nhờ đó mà hiệu suất cũng được cải thiện
Tôi nghĩ việc nhiều người dùng cùng truy cập tới file Excel của chúng ta thì ít có cơ hội nhưng như ở trên cũng đã trình bày, tùy thuộc vào kiểu con trỏ mà dữ liệu (Recordset) chúng ta lấy được có thể biến đổi. Bài viết này tôi không thực sự lưu tâm tới vấn đề này.
TênÝ nghĩa
adLockBatchOptimisticKhóa dùng chung để cập nhật hàng loạt
adLockOptimisticKhóa dùng chung
adLockPessimisticKhóa độc quyền
adLockReadOnlyChỉ cho phép đọc
adLockUnspecifiedKhông chỉ định khóa
Trong bài viết ày, ta quan tâm tới mục số 2 và số 3: adLockOptimistic, adLockPessimistic. Đây là hai chế độ cho phép ta có thể cập nhật dữ liệu.
Mục số 1 : adLockBatchOptimistic dùng để cập nhật hàng loạt, tôi sẽ trình bày sau.
Mục số 4 và số 5: adLockReadOnly, adLockUnspecified. Về cơ bản người ta không dùng hai chế độ này để cập nhật dữ liệu.

Cuối cùng là phương thức Update. Với mỗi bản ghi ta thực hiện việc update, nó tương đương với việc ta cập nhật worksheet trên Excel.
Mã:
Do Until rs.EOF
        rs!Gia = Val(rs!Gia) + 1
        rs.Update
        rs.MoveNext
Loop
 
Top