Nguyen thi huyen trang
Nguyen thi huyen trang PRO
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 185 lượt xem

VBA FOR SQL CONNECTION IN EXCEL

May 14 2022
  • 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

 

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ất
rõ 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 dungKết nối Early BindingKế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.Connection
Set 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).Row
Sheet1.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.Close
End 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).Row
Sheet1.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.Close
End Sub

Ưu điểmLà 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 Object
Reconrdset 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

Cùng tham gia cộng đồng hỏi đáp về chủ đề Lập trình

Thảo luận 0 câu trả lời
Lượt xem 185 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội