VBA FOR SQL CONNECTION IN EXCEL

Nguyen thi huyen trang
Nguyen thi huyen trang
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

VBA FOR SQL CONNECTION IN EXCEL

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)

VBA FOR SQL CONNECTION IN EXCEL

 

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

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

@ 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