8 Bước cơ bản để để thiết lập một câu lệnh SQL trong Excel

Ngày tạo 09/06/2021

- 2.131 Lượt xem

Quy trình 8 bước chuẩn mực để thiết lập kết nối ADODB.Connection và thực thi câu lệnh truy vấn SQL trực tiếp trong Excel VBA, giúp trích xuất và làm sạch hàng trăm nghìn dòng dữ liệu chỉ trong vài giây.

Bước 1: Tạo đối tượng lấy dữ liệu

Để tiến hành truy cập vào nơi chứa dữ liệu thì đầu tiên ta phải tạo ra một phương thức kết nối với nơi chứa dữ liệu gọi là ADODB.Connection.

MÃ NGUỒN VBA / SQLExcel VBA / ADODB

Dim Duy As Object
Set Duy = CreateObject("ADODB.Connection")

Lưu ý: "Duy" là tên đối tượng có thể thay đổi theo ý muốn.

Bước 2: Tạo túi chứa dữ liệu

Sau khi đã tạo ta đối tượng lấy dữ liệu, tiếp theo ta tạo túi chứa dữ liệu tên "rst" với thuộc tính "Recordset"

MÃ NGUỒN VBA / SQLExcel VBA / ADODB

Dim rst As Object
Set rst = CreateObject("ADODB.Recordset")

Bước 3: Lấy đường dẫn

MÃ NGUỒN VBA / SQLExcel VBA / ADODB

Dim duong_dan As String
duong_dan = "C:\SQL\Database_SQL.xlsx"

Lưu ý: C:\SQL\Database_SQL.xlsx là toàn bộ đường dẫn của file chứa dữ liệu cần lấy.

Bước 4: Mở đường dẫn đi vào nơi chứa dữ liệu

MÃ NGUỒN VBA / SQLExcel VBA / ADODB

Duy.Open ("Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " & duong_dan & ";Extended Properties = Excel 12.0")

Bước 5: Cho dữ liệu vào túi và lọc dữ liệu theo điều kiện của bạn

Ở bước này, sẽ tiến hành thực hiện các câu lệnh như SELECT, GROUP BY,... để thực hiện hành động truy vấn dữ liệu theo yêu cầu của người dùng (Các câu lệnh sẽ được trình bày ở những bài viết sau).

MÃ NGUỒN VBA / SQLExcel VBA / ADODB

rst.Open ("SELECT BatNbr,Crtd_User,DocType,InvcNbr FROM [APDoc$]"), Duy

Bước 6: Làm sạch dữ liệu

Tại vùng để trả kết quả ta cần phải xóa dữ liệu cũ tại vùng này đưa dữ liệu mới sau khi truy vấn vào.

MÃ NGUỒN VBA / SQLExcel VBA / ADODB

Sheet2.Range("A1:V150000").ClearContents

Chú thích: | "Sheet2" là tên sheetcode tại file chúng ta đang thao tác lập trình. | Range("A1:V150000") là vùng cần xóa và có thể thay đổi vùng này tùy vào nhu cầu dùng | ClearContents là cú pháp xóa nội dung trong ô tính excel

Bước 7: Lấy tên các cột của dữ liệu từ túi dữ liệu "rst"

Sau khi SELECT các cột ở bước 5, ta cần phải đưa các header của các cột vào vùng chứa dữ liệu. Cụ thể các header lần lượt ở đây sẽ là: BatNbr, Crtd_User, DocType, InvcNbr.

MÃ NGUỒN VBA / SQLExcel VBA / ADODB

Dim i As Integer
For i = 1 To rst.Fields.Count
Sheet2.Cells(1, i).Value = rst.Fields(i - 1).Name
Next

Chú thích:

Dim i as Interger: khởi tạo biến i
rst.Fields.Count: đếm số cột đã Select được ở bước 5 (cụ thể là có 5 cột => rst.Fields.Count = 5)
Sheet2.Cells(1, i).Value = rst.Fields(i - 1).Name: tên từng cột được mang qua ô Cell(1,i)
Ý nghĩa vòng lặp For - Next: Với mỗi biến "i" chạy từ 1-5 thì sẽ mang từng tên của cột tương ứng qua vùng chứa kết quả

Bước 8: Sao chép các dữ liệu từ túi dữ liệu qua vùng chứa kết quả

MÃ NGUỒN VBA / SQLExcel VBA / ADODB

Sheet2.Range("A2").CopyFromRecordset rst

Chú thích: | Sheet2 là sheet chứa kết quả | Range("A2") là ô trả kết quả

Mẹo Tối ưu Truy vấn SQL trong Excel VBA & Xử lý Lỗi Thường gặp

Khi triển khai câu lệnh truy vấn SQL thông qua kết nối ADODB.Connection trên nền tảng Microsoft Excel, bạn cần lưu ý 3 quy tắc vàng sau để đảm bảo tốc độ và độ chính xác tuyệt đối:

CHUỖI KẾT NỐI & CÚ PHÁP CHUẨNMicrosoft ACE OLEDB 12.0

'' Chuỗi kết nối chuẩn hỗ trợ cả cột trộn kiểu dữ liệu Số và Văn bản (IMEX=1):
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & _
        ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

'' Quy tắc bọc tên Sheet và tên Cột có khoảng trắng hoặc tiếng Việt:
strSQL = "SELECT [Mã Hàng], [Tên Sản Phẩm], SUM([Thành Tiền]) AS TongTien " & _
         "FROM [Data_BanHang$A1:G50000] " & _
         "WHERE [Thành Tiền] IS NOT NULL " & _
         "GROUP BY [Mã Hàng], [Tên Sản Phẩm]"

Luôn thêm tham số IMEX=1: Giúp trình điều khiển OLEDB không tự ý biến các mã hàng vừa có số vừa có chữ thành giá trị Null.
Giới hạn vùng dữ liệu khi Sheet có định dạng thừa: Thay vì gọi [Sheet1$], bạn có thể chỉ định rõ [Sheet1$A1:Z50000] để bỏ qua các dòng rác cuối bảng.
Đóng kết nối sau khi CopyFromRecordset: Luôn gọi rs.Close và cn.Close để giải phóng bộ nhớ RAM và tránh khóa file nguồn.