HƯỚNG DẪN TẠO CÂU LỆNH NÚT THÊM MỚI TRONG VBA EXCEL

Xem tài liệu đính kèm VBA for SQL references hoặc VBA for connection with SQL

INSERT INTO [bảng tính] (tên các cột/trường) VALUES (Các giá trị cần thêm)

Khi bạn làm trên phần mềm, hoặc VBA bạn sẽ thấy có những nút (button) thêm mới hoặc là Save dữ liệu vào bảng dữ liệu nào đó. Chắc hẳn bạn đã từng thắc mắc không biết người ta tạo ra form sử dụng như thế nào mà khi nhập dữ liệu vào thì tự động các giá trị bạn thêm vào sẽ cập nhật vào bảng tính của bạn mà bạn không cần phải copy dữ liệu hay nhập liệu thủ công  như di sang các cột để nhập dữ liệu.

Đầu tiên bạn hãy tạo cho mình 1 form như sau. Bạn ấn Alt+F11 để mở ra cửa sổ nhập code VBA. Sau đó click chuột phải và chọn Insert -→ User Form như sau

Sau đó bạn tạo form bằng công cụ ToolBox. Ví dụ sau đây mình sẽ tạo một User form thông tin sinh viên như hình dưới. Tên của User Form này là Uf_Hoc_Vien. Bạn hãy đặt tên giống như mình nhé để tiện việc viết code

Ở đây mình sử dụng Sheet có tên là Hoat_Dong. Sheet này để lưu thông tin các học viên từ Form bạn vừa tạo. Tại sheet Hoat_Dong, bạn tạo 1 form khác trên excel để lưu giá trị các textbox tương ứng vào từng ô. Ví dụ Mã học viên (textbox trên form là txt_Ma_HV sẽ tương ứng với ô H1) Tên Học Viên (txt_TenHocVien sẽ tương ứng với ô H2), Số điện thoại (txt_Phone sẽ tương ứng với ô H3) và Địa Chỉ (txt_DiaChi sẽ tương ứng với ô H4). Số ID ở đây chính là số dòng chọn, ô này bạn để trống mình sẽ dùng cho nút Sửa ở bài viết khác. Và bạn hãy đặt tên cho bảng tính của mình từ ô A1:E2000 là bảng  [THONGTIN_HV] nhé

Bạn Click vào ô trống bất kỳ trên Form Uf_Hoc_Vien để viết code gán vào các ô H1,H2,H3,H4 nhé

Option Explicit


'Tạo nút thêm học viên

Private Sub cmb_Them_Click()
Call Them_thongtin_hv
End Sub

'Gán giá trị ô H1 tương ứng Mã học viên
Private Sub txt_Ma_HV_Change()
  Sheets("HOAT_DONG").Range("H1") = txt_Ma_HV.Value
 
End Sub

'Gán giá trị ô H2 tương ứng Tên học viên
Private Sub txt_TenHocVien_Change()
 Sheets("HOAT_DONG").Range("H2") = txt_TenHocVien.Value
 
End Sub


'Gán giá trị ô H3 tương ứng Số điện thoại
Private Sub txt_Phone_Change()
 Sheets("HOAT_DONG").Range("H3").Value = txt_Phone.Value
 End Sub

' Gán giá trị ô H4 tương ứng Địa chỉ
Private Sub txt_DiaChi_Change()
 Sheets("HOAT_DONG").Range("H4") = txt_DiaChi.Value
End Sub

Xem thêm: VBA FOR SQL WITH SQL_SELECT

Tương tự như module tạo UserForm, bạn hãy ấn chuột phải vào vị trí trống bên trái màn hình viết code VBA và chọn Insert==> Module để tạo 1 kết nối câu lệnh SQL. Code viết như sau

Option Explicit
Sub Them_thongtin_hv()
'Khai bao bien - Ket noi LateBinding
    Dim Cnn As Object
    Dim lrs As Object
    Dim cmd As Object
    Dim giatri As Long
    Dim Pasword As String, path As String
    
 ' Thiet lap ADODB  voi ket noi LateBinding
    
    Set Cnn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.command")
    
'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
    
' Viet cau lenh SQL tai o A1 trong sheet SQL. Thuc thi lenh voi Database.
'Thuong dung viet cac lenh voi Insert (Them)


'Câu lệnh SQL sẽ được lưu ở ô H6 trong sheet HOAT_DONG
With cmd
    .ActiveConnection = Cnn
     .CommandText = Sheets("HOAT_DONG").Range("H6").Value
     
    .Execute
End With


  
  'Dong lenh va ket noi
  
  Set cmd = Nothing
  Set Cnn = Nothing
  
End Sub

Để có thể viết câu lệnh SQL cho việc thêm vào danh sách học viên,  chúng ta có 3 cách viết câu lệnh như sau:

Cách 1: Viết câu lệnh SQL giống như viết hàm & nối chuỗi trong excel vào ô H6. Ví dụ: “ ' ” & HOAT_DONG!H1 & “ ' ”. Nhưng bạn phải lưu ý cách này sẽ phải viết dấu ‘ ’ vì định dạng kiểu text, nếu định dạng kiểu số thì không cần ‘ ’

=" INSERT INTO [THONGTIN_HV] ([MA_HV],[TEN_HV],[SDT],[DIA_CHI]) VALUES ( ' "&HOAT_DONG!H1&" ' , ' "&HOAT_DONG!H2&" ',  "&HOAT_DONG!H3 &" , ' "&HOAT_DONG!H4&" '  )"

Cách 2: Viết câu lệnh SQL bằng hàm GRANGE trong Addin của Gitiho vào ô H6. Với cách này ngắn gọn hơn và bạn không phải định dạng kiểu cho từng ô. Ở đây các đối số 4 là định dạng kiểu text cho từng ô. Còn đối số 3 là định dạng kiểu số. Trong VD này mình định dạng ô H3 (số điện thoại) là kiểu số

=" INSERT INTO [THONGTIN_HV] ([MA_HV],[TEN_HV],[SDT],[DIA_CHI]) VALUES ( "& GRANGE(H1,4) & "," & GRANGE(H2,4)& "," &GRANGE(H3,3) &"," &GRANGE(H4,4) &")"

Cách 3: Viết câu lệnh SQL trực tiếp vào trong code VBA

With cmd
    .ActiveConnection = Cnn
     .CommandText = " INSERT INTO [THONGTIN_HV] ([MA_HV],[TEN_HV],[SDT],[DIA_CHI]) VALUES ( ' " & Sheets("HOAT_DONG").Range("H1") _
     & "','" & Sheets("HOAT_DONG").Range("H2") & "'," & Sheets("HOAT_DONG").Range("H3") & ",'" & Sheets("HOAT_DONG").Range("H4") & "')"
     
    .Execute
End With

Xem thêm: BẠN ĐÃ BAO GIỜ SỬ DỤNG ADDIN CỦA GITIHO CHƯA?