Vba For Sql Connection In Excel

• SQL FOR REFERENCES - THAM KHẢO CÁC CÂU LỆNH SQL TẠI ĐÂY
• KHÁI NIỆM SQL VÀ CÁCH KẾT NỐI TRONG VBA

File đính kèm

1. SQL là gì?

2. Kết nối SQL như thế nào trong Excel

3. Phân biệt 2 kiểu kết nối: Early Binding và Late Binding trong Excel VBA

4. Hàm kiểm tra loại files

5. Code cho việc lấy dữ liệu từ câu Truy vấn Query vào bảng tính Excel

SQL LÀ GÌ

SQL là viết tắt của Structured Query Language, nghĩa là ngôn ngữ truy vấn dữliệu có cấu trúc , là một ngôn ngữ máy tính phổ biến để tạo, sửa, xoá hoặc lấy dữliệu từ một hệ quản trị dữ liệu Dưới đây là kết quả của 1 câu lệnh SQL

Kết nối SQL như thế nào trong Excel

Muốn sử đụng được SQL chúng ta cần kết nối cơ sở dữ liệu?

* Đầu tiên các bạn vào trang web sau:https://www.connectionstrings.com/* Ở đây chúng ta sử dụng kiểu kết nối sau nhé.Microsoft ACE OLEDB 12.0 connection strings - trong phần trang web này có nói rấtrõ về phần kết nối của excel tới sql * Để thực hiện kết nối dữ liệu trong Excel ta thiết lập kết nối ADODB connection như sau

Ấn Alt+F11: mở cửa sổ VBA==> Vào Tools ==> References (Chọn thư viện Recordset để kết nối)

Phân biệt 2 kiểu kết nối: Early Binding và Late Binding trong Excel VBA

Nội dung	Kết nối Early Binding	Kết nối Late Binding
Code VBA	Sub KetnoiEarlyBinding() 'Khai bao biến Dim cnn As ADODB.Connection Dim lrs As ADODB.Recordset Dim sqlQuery As String Dim dc As Long'Thiết lập kết nối ADODB bằng lệnh New Set cnn = New ADODB.ConnectionSet lrs = New ADODB.Recordset 'Tạo phiên bản kết nối của Microsoft cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 'Mở kết nối cnn.Open lrs.Open sqlQuery, cnn 'Xóa nội dung trong sheet 1 từ A1 đến dòng cuối dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).RowSheet1.Range("A1:X" & dc).ClearContents 'Sử dụng câu truy vấn SQL: Chọn tất cả các cột từ bảng dữ liệu có tên Customers sqlQuery = " SELECT * FROM Customers" 'Thực thi lệnh SQL Sheet1.Range("A1").CopyFromRecordset lrs 'Đóng kết nối lrs.Close cnn.CloseEnd Sub	Sub KetnoiLateBinding() 'Khai báo biến Dim cnn As Object Dim lrs As Object Dim sqlQuery As String Dim dc As Long 'Thiết lập kết nối ADODB bằng lệnh tạo đối tượng Object Set cnn = CreateObject("ADODB.Connection") Set lrs = CreateObject("ADODB.Recordset") 'Tạo phiên bản kết nối của Microsoft cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 'Mở kết nối cnn.Open lrs.Open sqlQuery, cnn 'Xóa nội dung trong sheet 1 từ A1 đến dòng cuối dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).RowSheet1.Range("A1:X" & dc).ClearContents 'Sử dụng câu truy vấn SQL: Chọn tất cả các cột từ bảng dữ liệu có tên Customers sqlQuery = " SELECT * FROM Customers" 'Thực thi lệnh SQLSheet1.Range("A1").CopyFromRecordset lrs 'Đóng kết nối lrs.Close cnn.CloseEnd Sub
Ưu điểm	Là phương pháp có hướng dẫn khi viết hàm	Khi chạy sang máy tính khác không cần phải tích chọn thư viện, vẫn hoạt động bình thường
Nhược điểm	Khi chạy sang máy tính khác bắt buộc phải vào Tool→References→Chọn Microsoft ActiveX Data ObjectReconrdset 2.8	Không có hướng dẫn khi viết hàm

Hàm kiểm tra loại Files

'1. Ham Kiem tra duong dan duoi file, 'tach chuoi duong dan sau dau "." la cac duoi file Excel va Access nhu xls, xlsx, xlsm,xlsb, mdb, accdb con lai la tra ve file SQL Function CheckPath(s As String) As String Dim kq As String, GetName As String GetName = Split(s, ".")(UBound(Split(s, "."))) GetName = LCase(GetName) If (GetName = "xls" Or GetName = "xlsx" Or GetName = "xlsm" Or GetName = "xlsb") Then kq = "EXCEL" ElseIf (GetName = "mdb" Or GetName = "accdb") Then kq = "ACCESS" Else kq = "SQLSEVER" End If CheckPath = kq End Function

Code cho việc lấy dữ liệu từ câu Truy vấn Query vào bảng tính Excel

Option Explicit 'Hien ket qua du lieu Sub REPORTS_SQL() 'Khai bao bien - Ket noi LateBinding Dim Cnn As Object Dim lrs As Object Dim SQLQuery As String, kq As Boolean Dim dc As Long, icols As Long Dim path As String, Pasword As String 'Xoa du lieu trong bang Ket Qua tu dong A3 den AO dong cuoi dc = Sheets("REPORTS").Range("A" & Sheets("REPORTS").Rows.Count).End(xlUp).Row Sheets("REPORTS").Range("A3:AO" & dc).ClearContents ' Thiet lap ADODB voi ket noi LateBinding Set Cnn = CreateObject("ADODB.Connection") Set lrs = CreateObject("ADODB.Recordset") 'kiem tra ket noi (xem them ham CheckPath) ' Neu duong dan la file .xls, .xlsm, .xlsb, .xlsx ==> CheckPath tra ve file Excel va ket noi theo phien ban OLEDB.12.0 hoac OLEDB.4.0 ' Neu duong dan la file .mdb, .accdb ==> CheckPath tra ve file Access (bao gom mat khau neu co) ' Con lai CheckPath tra ve file SQL ' xac dinh ten duong dan path = ActiveWorkbook.FullName ' kiem tra ket noi tren 3 loai file Excel, Access, SQL If (CheckPath(path) = "EXCEL") Then If Val(Application.Version) > 12 Then Cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=0"";" Else Cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=0"";" End If ElseIf (CheckPath(path) = "ACCESS") Then If Val(Application.Version) > 12 Then Cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Persist Security Info=False; Jet OLEDB:Database Password=" & Pasword & ";" Else Cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Persist Security Info=False; Jet OLEDB:Database Password=" & Pasword & ";" End If Else Cnn.ConnectionString = path End If 'Mo ket noi Cnn.Open 'Cau lenh SQL trong sheet SQL tai o A1 SQLQuery = Sheets("SQL").Range("A1").Value ' Mo ket noi cau lenh SQL lrs.Open SQLQuery, Cnn 'Tao tieu de cho tung cot muon lay du lieu tu Query For icols = 0 To lrs.Fields.Count - 1 Sheets("REPORTS").Cells(3, icols + 1).Value = lrs.Fields(icols).Name Next 'Ghi lai du lieu bat dau tu vung ô A4 Sheets("REPORTS").Range("A4").CopyFromRecordset lrs 'Ghi lai cac cau lenh SQL sang sheet "Record_SQL" Call Record_SQL_ThemLenh 'Dong ket noi lrs.Close: Set lrs = Nothing Cnn.Close: Set Cnn = Nothing 'Chuyen sang file ket qua Call sheet_select(Sheets("REPORTS")) End Sub